如何在ms-sql中不使用秩/行_number函数获得每个类别的前2名

Question

我正在使用MS-SQL 2016。

我有一个方案需要为每个组获得前2名的奖金，不允许使用级别和ROW_NUMBER，下面是生成表和数据的代码：

Create table Freelancer (id int, fl_name varchar(20), bonus int, fl_group varchar(50))


insert into Freelancer (id, fl_name, bonus, fl_group) Values (1, 'John', 1000, 'SQL')
insert into Freelancer (id, fl_name, bonus, fl_group) Values (2, 'Jane', 990, 'MySQL')
insert into Freelancer (id, fl_name, bonus, fl_group) Values (3, 'Jimmy', 320, 'Oracle')
insert into Freelancer (id, fl_name, bonus, fl_group) Values (4, 'Jeff', 802, 'DynamoDB')
insert into Freelancer (id, fl_name, bonus, fl_group) Values (5, 'Johnathan', 2345, 'Hive')
insert into Freelancer (id, fl_name, bonus, fl_group) Values (6, 'Jeffery', 321, 'RDS')
insert into Freelancer (id, fl_name, bonus, fl_group) Values (7, 'Jane2', 1990, 'MySQL')
insert into Freelancer (id, fl_name, bonus, fl_group) Values (8, 'Jimmy3', 321, 'Oracle')
insert into Freelancer (id, fl_name, bonus, fl_group) Values (9, 'Jeff4', 803, 'DynamoDB')
insert into Freelancer (id, fl_name, bonus, fl_group) Values (10, 'Johnathan5', 345, 'Hive')
insert into Freelancer (id, fl_name, bonus, fl_group) Values (11, 'Jeffery', 32, 'RDS')
insert into Freelancer (id, fl_name, bonus, fl_group) Values (12, 'Jane3', 1190, 'MySQL')
insert into Freelancer (id, fl_name, bonus, fl_group) Values (13, 'Jimmy4', 322, 'Oracle')
insert into Freelancer (id, fl_name, bonus, fl_group) Values (14, 'Jeff5', 8002, 'DynamoDB')
insert into Freelancer (id, fl_name, bonus, fl_group) Values (15, 'Johnathan6', 235, 'Hive')
insert into Freelancer (id, fl_name, bonus, fl_group) Values (16, 'Jeffery7', 31, 'RDS')
insert into Freelancer (id, fl_name, bonus, fl_group) Values (17, 'Jack', 34, 'Redshift')
insert into Freelancer (id, fl_name, bonus, fl_group) Values (18, 'Jennifer', 121, 'Aurora')
insert into Freelancer (id, fl_name, bonus, fl_group) Values (19, 'Jackson', 425, 'Redis')

例如，在DynamoDB组，Jeff5(8002)和Jeff4(803)将被选中。

我已经尝试的内容如下，尽管我在子句中指定了前2名，但没有返回所需的结果：

select *
from Freelancer t
where t.ID in (
    select top 2 ID
    from Freelancer tt
    where tt.fl_name = t.fl_name
    order by tt.bonus desc
    )
order by fl_group, bonus desc

我的结果是：

​

​

Answer 1

请尝试这个解决方案，听说在这个解决方案中，我已经将fl_group合并为您的类别，并获得fl_group明智的前2项记录。

声明临时表

declare @Freelancer as TABLE (id int, fl_name varchar(20), bonus int, fl_group varchar(50))

插入示例数据

insert into @Freelancer(id, fl_name, bonus, fl_group) Values(1, 'John', 1000, 'SQL')
,(2, 'Jane', 990, 'MySQL')
,(3, 'Jimmy', 320, 'Oracle')
,(4, 'Jeff', 802, 'DynamoDB')
,(5, 'Johnathan', 2345, 'Hive')
,(6, 'Jeffery', 321, 'RDS')
,(7, 'Jane2', 1990, 'MySQL')
,(8, 'Jimmy3', 321, 'Oracle')
,(9, 'Jeff4', 803, 'DynamoDB')
,(10, 'Johnathan5', 345, 'Hive')
,(11, 'Jeffery', 32, 'RDS')
,(12, 'Jane3', 1190, 'MySQL')
,(13, 'Jimmy4', 322, 'Oracle')
,(14, 'Jeff5', 8002, 'DynamoDB')
,(15, 'Johnathan6', 235, 'Hive')
,(16, 'Jeffery7', 31, 'RDS')
,(17, 'Jack', 34, 'Redshift')
,(18, 'Jennifer', 121, 'Aurora')
,(19, 'Jackson', 425, 'Redis')

类别-明智的2项记录

select *
from @Freelancer t
where t.ID in (
    select top 2 ID
    from @Freelancer tt
    where tt.fl_group = t.fl_group
    order by id
    )
order by fl_group,id

输出

​

​

在这里，您还可以使用join而不是子查询来提高查询性能。

有关更多信息，您可以尝试此解决方案--请访问以下网址：查询编辑器

我希望这对你有用。

Answer 2

您应该比较外部查询和子查询之间的奖金，并且应该使用组进行关联。

SELECT *
FROM Freelancer t
WHERE t.bonus IN (
    SELECT TOP 2 tt.bonus
    FROM Freelancer tt
    WHERE tt.fl_group = t.fl_group
    ORDER BY tt.bonus DESC
)
ORDER BY
    fl_group,
    bonus DESC;

Answer 3

如果可以使用其他窗口函数，请尝试将ROW_NUMBER()行为替换为带窗口的SUM()。

SELECT 
    id, fl_name, bonus, fl_group
FROM (
    SELECT 
       *,
       SUM(1) OVER (PARTITION BY fl_group ORDER BY bonus) AS Rn
    FROM Freelancer
) t
WHERE Rn <= 2