如何在不使用 ms-sql 中的 rank/row_number 函数的情况下获得每个类别的前 2 名

mdivk 发表于 Dev

影音

我使用的是 MS-SQL 2016。

我有一个场景，需要为每个组获得前 2 名的奖金，RANK 和 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

我的结果：

尼库尼·萨塔西亚

请尝试此解决方案，在此解决方案中听到我已将 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

输出