数据如上
id goods_id price start_time
1 1161 54 2012
2 1162 88 201
3 1163 49 2067
4 1164 200 2012
5 1165 280 2512
6 1166 29 2012
7 1167 280 2012
8 1168 280 2012
9 1169 240 2512
10 11610 160 2012
11 11611 17 2252
12 11612 23 2012希望结果 按照 goods_id 分组后 获取state_time第二大的记录求sql 最好没有sqlserver特有的函数
id goods_id price start_time
1 1161 54 2012
2 1162 88 201
3 1163 49 2067
4 1164 200 2012
5 1165 280 2512
6 1166 29 2012
7 1167 280 2012
8 1168 280 2012
9 1169 240 2512
10 11610 160 2012
11 11611 17 2252
12 11612 23 2012希望结果 按照 goods_id 分组后 获取state_time第二大的记录求sql 最好没有sqlserver特有的函数
解决方案 »
- 一条MySQL语句的问题
- mysql表内容批量更新
- 求助一条插入语句!
- MySQLAdministrator无法编辑表中数据 系统找不到指定的文件
- mysql-cluster 备份和恢复该如何做
- win7下装mysql出现apply security settings 出错
- mysql_query()函数查询求助
- 问大家一个很菜的问题,mysql -h后面添什么,127.0.0.1还是计算机名
- phpmyadmin导入CSV时报错Invalid parameter for CSV import转义字段的字符
- 这个怎么解决 网上的那些都试过了 具体一点
- 配置Proxool的时候出现异常Proxool Provider unable to load JAXP configurator file: ProxoolC
- mysql 一个存储过程内部顺序执行还是并发执行的
*
from(
select px=row_number()over(partition by goods_id order by state_time desc),
* from tb
)t
where px=2
悲剧的高速你 我其实是在mysql上查询 所以没row_number函数啊
mysql版太冷清 所以发这里了!
go
create table [TestTB] (id int,goods_id int,price int,start_time int)
insert into [TestTB]
select 1,1161,54,2012 union all
select 2,1161,88,201 union all
select 3,1161,49,2067 union all
select 4,1162,200,2012 union all
select 5,1162,280,2512 union all
select 6,1162,29,2012 union all
select 7,1167,280,2012 union all
select 8,1168,280,2012 union all
select 9,1169,240,2512 union all
select 10,11610,160,2012 union all
select 11,11611,17,2252 union all
select 12,11612,23,2012select * from [TestTB]
SELECT *
FROM TestTB A
WHERE id IN ( SELECT TOP 1
id
FROM ( SELECT TOP 2
id ,
start_time
FROM testtb b
WHERE A.goods_id = b.goods_id
ORDER BY start_time DESC
) A
ORDER BY start_time ASC )
/*
id goods_id price start_time
1 1161 54 2012
6 1162 29 2012
7 1167 280 2012
8 1168 280 2012
9 1169 240 2512
10 11610 160 2012
11 11611 17 2252
12 11612 23 2012*/
where state_time=(select top 1 state_time from tb where goods_id=a.goods_id
and state_time<(select max(state_time) from tb where goods_id=a.goods_id))
SELECT 1,1161,54,2012 UNION ALL
SELECT 2,1161,88,201 UNION ALL
SELECT 3,1161,49,2067 UNION ALL
SELECT 4,1162,200,2012 UNION ALL
SELECT 5,1162,280,2512 UNION ALL
SELECT 6,1162,29,2012 UNION ALL
SELECT 7,1167,280,2012 UNION ALL
SELECT 8,1168,280,2012 UNION ALL
SELECT 9,1169,240,2512 UNION ALL
SELECT 10,11610,160,2012 UNION ALL
SELECT 11,11611,17,2252 UNION ALL
SELECT 12,11612,23,2012) a;SELECT * FROM tta4 a WHERE 2=(SELECT COUNT(*) FROM tta4 WHERE a.`goods_id`=`goods_id` AND a.`start_time`>`start_time`)
from 数据如上 t
where (select count(*) from where goods_id=t.goods_id and start_time>t.start_time)=1