数据如上
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特有的函数
解决方案 »
- 数据库初学者求救建表的问题
- 为何我的一条insert语句插入两条数据?
- 一个简单MySql的查询语句,你能查出来吗?
- 奇怪 为什么我电脑的my.ini里没有 query_cache_type
- 如何在表的某个字段加约束
- 新手求助:如何删除unique约束
- EMSMySQLManager导入,导出问题
- 好消息,MySQL 4.1 正式发布!!!
- 一条简单的SQL语句把我拦住了!
- now()获取系统时间差8小时
- 配置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