22,209
社区成员
发帖
与我相关
我的任务
分享
;WITH t1(序号,期号,标识1) AS
(
select 1,1001,3 union all
select 2,1002,5 union all
select 3,1003,5 union all
select 4,1004,6 union all
select 5,1005,4 union all
select 6,1006,7 union all
select 7,1007,9 union all
select 8,1008,0 union all
select 9,1009,4
),
t2 AS
(SELECT row_number()
OVER(PARTITION BY 标识1 ORDER by 期号 DESC) AS rn , *
from t1
)
SELECT TOP 5 序号,期号,标识1
from t2
WHERE rn = 1
ORDER by 期号 DESC
/*
9 1009 4
8 1008 0
7 1007 9
6 1006 7
4 1004 6
*/
create table #t
(序号 int, 期号 int, 标识1 int)
insert into #t
select 1,1001,3 union all
select 2,1002,5 union all
select 3,1003,5 union all
select 4,1004,6 union all
select 5,1005,4 union all
select 6,1006,7 union all
select 7,1007,9 union all
select 8,1008,0 union all
select 9,1009,4
with t as(select 标识1,rn=row_number() over(order by 期号 desc)
from #t)
select top 5 a.标识1
from t a
where not exists(select 1 from t b where b.标识1=a.标识1 and b.rn<a.rn)
order by a.rn
/*
标识1
-----------
4
0
9
7
6
(5 row(s) affected)
*/