选择每个帐户SQL Server的最多两行

I have this table

我有这张桌子

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

And I need a query that returns the two max age from each ACCNUM in this case, records:

在这种情况下,我需要一个从每个ACCNUM返回两个最大年龄的查询,记录:

1, 2, 4, 5, 6

I have tried too many queries but nothing works for me.

我尝试过太多查询,但对我来说没什么用。

I tried this query

我试过这个查询

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)

3 个解决方案

#1

TSQL Ranking Functions: Row_Number() https://msdn.microsoft.com/en-us/library/ms186734.aspx

TSQL排名函数:Row_Number()https://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

#2

You can use row_number():

你可以使用row_number():

select accnum
     , age
from ( select accnum
            , age
            , row_number() over(partition by accnum order by age desc) as r
       from table1 as T1) t where r < 3

#3

CODE:

WITH CTE AS (SELECT ID, AGE, ACCNUM, NAME,
             ROW_NUMBER() OVER(PARTITION BY ACCNUM ORDER BY AGE DESC) AS ROW_NUM
             FROM T1)
SELECT ID, AGE, ACCNUM, NAME
FROM CTE
WHERE ROW_NUM <= 2

Uses a common table expression to achieve the desired result.

使用公用表表达式来实现所需的结果。

SQL Fiddle

#1