我有这张桌子
ID AGE ACCNUM NAME
--------------------------------
1 10 55409 Intro
2 6 55409 Chapter1
3 4 55409 Chapter2
4 3 69591 Intro
5 6 69591 Outro
6 0 40322 Intro
ACCNUM在这种情况下，我需要一个查询，从每个查询返回两个最大年龄，记录：
1, 2, 4, 5, 6
我尝试了太多查询，但对我没有任何帮助。
我试过这个查询
Select
T1.accnum, T1.age
from
table1 as T1
inner join
(select
accnum, max(age) as max
from table1
group by accnum) as T2 on T1.accnum = T2.accnum
and (T1.age = T2.max or T1.age = T2.max -1)
TSQL排名功能：https：Row_Number() //msdn.microsoft.com/en-us/library/ms186734.aspx
select id, age, accnum, name
from
(
select id, age, accnum, name, ROW_NUMBER() Over (Partition By accnum order by age desc) as rn
from yourtable
) a
where a.rn <= 2
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句