表T
A B C
20A 2006-07-04 AB
19A 2006-09-03 CC
20A 2006-09-14 CD
19A 2006-07-04 DB
18 2005-09-03 AA
20A 2006-05-03 GG要求显示如下
A B C
20A AB CD
19A CC DB
18 GG NULL
查出最新的两条记录的C,B显示最新的C,C显示倒数第二新的C,不知道有没有什么好的语句。先谢谢各位了。
A B C
20A 2006-07-04 AB
19A 2006-09-03 CC
20A 2006-09-14 CD
19A 2006-07-04 DB
18 2005-09-03 AA
20A 2006-05-03 GG要求显示如下
A B C
20A AB CD
19A CC DB
18 GG NULL
查出最新的两条记录的C,B显示最新的C,C显示倒数第二新的C,不知道有没有什么好的语句。先谢谢各位了。
正确为:
a b c
---------- ---------- ----------
20A CD AB
19A CC DB
18 AA NULL
if object_id('pub..tb') is not null
drop table tb
gocreate table tb
(
a varchar(10),
b datetime,
c varchar(10)
)insert into tb(a,b,c) values('20A','2006-07-04','AB')
insert into tb(a,b,c) values('19A','2006-09-03','CC')
insert into tb(a,b,c) values('20A','2006-09-14','CD')
insert into tb(a,b,c) values('19A','2006-07-04','DB')
insert into tb(a,b,c) values('18' ,'2005-09-03','AA')
insert into tb(a,b,c) values('20A','2006-05-03','GG')if object_id('pub..test') is not null
drop table test
goselect px=(select count(1) from tb where a=t.a and b>t.b)+1 , a,b,c into test from tb t
order by a , px select a,
b = max(case when px = 1 then c else null end),
c = max(case when px = 2 then c else null end)
from test
group by a
order by a descdrop table tb
drop table test
a b c
---------- ---------- ----------
20A CD AB
19A CC DB
18 AA NULL
(
a varchar(10),
b datetime,
c varchar(10)
)insert into @t(a,b,c) values('20A','2006-07-04','AB')
insert into @t(a,b,c) values('19A','2006-09-03','CC')
insert into @t(a,b,c) values('20A','2006-09-14','CD')
insert into @t(a,b,c) values('19A','2006-07-04','DB')
insert into @t(a,b,c) values('18' ,'2005-09-03','AA')
insert into @t(a,b,c) values('20A','2006-05-03','GG')
select t1.a,(select top 1 t2.c from @t t2 where t2.a=t1.a order by t2.b desc) as b,
(select top 1 t3.c from @t t3 where t3.a=t1.a and t3.b<max(t1.b)order by t3.b desc) as c
from @t t1 group by t1.a
--结果
a b c
---------- ---------- ----------
18 AA NULL
19A CC DB
20A CD AB
drop table tb
gocreate table tb
(
a varchar(10),
b datetime,
c varchar(10)
)insert into tb(a,b,c) values('20A','2006-07-04','AB')
insert into tb(a,b,c) values('19A','2006-09-03','CC')
insert into tb(a,b,c) values('20A','2006-09-14','CD')
insert into tb(a,b,c) values('19A','2006-07-04','DB')
insert into tb(a,b,c) values('18' ,'2005-09-03','AA')
insert into tb(a,b,c) values('20A','2006-05-03','GG')select a,
b = max(case when px = 1 then c else null end),
c = max(case when px = 2 then c else null end)
from
(
select px=(select count(1) from tb where a=t.a and b>t.b)+1 , a,b,c
from tb t
) t
group by a
order by a descdrop table tb
a b c
---------- ---------- ----------
20A CD AB
19A CC DB
18 AA NULL(所影响的行数为 3 行)
max 和min 都是 返回1组数中最大/小的一行
(case when px=1 then c else null end)返回1行数据 所以结果都一样