sql - 如何为每个人选择前N个薪资？

Question

我有 table

user_id  salary month
1        100    1
1        150    2
1        200    3
1        180    4
1        140    5
2        10     1
2        40     2
2        20     3
2        15     4
2        45     5

我想为每个人选择前 2 个薪资。

我尝试理解交叉应用。看起来我发明的任务符合交叉应用。

现在我有以下查询

select distinct(s.user_id) from Salary s
cross apply (
     select top 2 * from Salary  sal
     order by sal.salary desc
)sal

看起来我离预期结果还很远。

预期结果:

1    180
1    200
2    40
2    45

Answer 1

您可以将 OUTER APPLY 与 TOP 2 一起使用:

SELECT DISTINCT 
            y.[user_id],
            d.salary,
            d.[month]
FROM YourTable y
OUTER APPLY(
    SELECT TOP 2  *
    FROM YourTable
    WHERE y.[user_id] = [user_id]
    ORDER BY [user_id], salary DESC
    ) as d
ORDER BY [user_id], salary DESC

将返回:

user_id salary  month
1       200     3
1       180     4
2       45      5
2       40      2

另一种方式:

;WITH cte AS (
SELECT  *,
        ROW_NUMBER() OVER (PARTITION BY [user_id] ORDER BY salary DESC) as rn
FROM YourTable
)

SELECT [user_id], salary, [month]
FROM cte 
WHERE rn <= 2

相同的输出。