ID A B C Price
1 11 13 14 100
2 11 12 14 200
3 11 15 14 500
4 11 16 14 10005 12 2 14 1000
6 12 1 14 1000
7 12 3 14 1000
8 12 4 14 1000怎么取得 Group by 的每组中price第二小的记录
比如第一组数据中,取得 200的那条数据
第二组中,无所谓取得什么样的数据..
1 11 13 14 100
2 11 12 14 200
3 11 15 14 500
4 11 16 14 10005 12 2 14 1000
6 12 1 14 1000
7 12 3 14 1000
8 12 4 14 1000怎么取得 Group by 的每组中price第二小的记录
比如第一组数据中,取得 200的那条数据
第二组中,无所谓取得什么样的数据..
declare @t table(ID int,A int,B int,C int,Price int)
insert @t
select 1, 11, 13, 14, 100 union all
select 2, 11, 12, 14, 200 union all
select 3, 11, 15, 14, 500 union all
select 4, 11, 16, 14, 1000 union all
select 5, 12, 2, 14, 1000 union all
select 6, 12, 1, 14, 1000 union all
select 7, 12, 3, 14, 1000 union all
select 8, 12, 4, 14, 1000select * from @t a
where (select count(*) from @t where A = a.A and Price < a.Price) = 1
insert into #(a,b,c,price)
select 11,13,14,100
union all select 11,12,14,200
union all select 11,15,14,500
union all select 11,16,14,1000
union all select 12,2,14,1000
union all select 12,1,14,1000
union all select 12,3,14,1000
union all select 12,4,14,1000select #.* from # where id in (select top 1 id from (select top 2 id,price from # a where a.a=#.a order by price)t order by price desc)drop table #
只出了200啊,没出一个1000啊
eclare @t table(ID int,A int,B int,C int,Price int)
insert @t
select 1, 11, 13, 14, 100 union all
select 2, 11, 12, 14, 200 union all
select 3, 11, 15, 14, 500 union all
select 4, 11, 16, 14, 1000 union all
select 5, 12, 2, 14, 1000 union all
select 6, 12, 1, 14, 1000 union all
select 7, 12, 3, 14, 1000 union all
select 8, 12, 4, 14, 1000
----生成排序用的含有IDENTITY列的临时表
select id2 = identity(int,1,1),* into #tmp from @t order by A,Price
----根据IDENTITY列查找
select ID,A,B,C,Price from #tmp a
where (select count(*) from #tmp where A = a.A and id2 < a.id2) = 1
----清除测试环境
drop table #tmp
结贴.
insert into A(a,b,c,price)
select 11,13,14,100
union all select 15,12,13,100
union all select 11,12,14,200
union all select 11,15,14,500
union all select 11,16,14,1000
union all select 12,2,14,1000
union all select 12,1,14,1000
union all select 12,3,14,1000
union all select 12,4,14,1000
union all select 13,5,15,1000
union all select 13,4,13,1000
select * from A
select * from A where id in (select top 1 id from (select top 2 id,price from A b where b.a=A.a order by price)t order by price desc)drop table A你把这个运行一下就知道他是错的了。