时间戳

首页 > 解决方案 > 使用自定义排序顺序 SQL Server 删除没有 CTE 的重复项

sql - 使用自定义排序顺序 SQL Server 删除没有 CTE 的重复项

问题描述

我有下表：

UserName | Score | Position | Class
Larry    | 0.12   | 1       | 2
Larry    | 0.13   | 2       | 2
Larry    | 0.12   | 3       | 2
Gale     | 0.112  | 4       | 2
Smith    | 0.1    | 5       | 2
Gale     | 0.21   | 6       | 2
Smith    | 0.42   | 7       | 2
Gale     | 0.112  | 8       | 2
Smith    | 0.42   | 9       | 2

我需要的是获得属于Class 2的唯一UserName，以及最高分和最佳（最低）位置，即从重复项中选择得分最高和位置最低的一个。最后，使用最高分和最低位置对最终结果集进行排序。例如，上表应返回：

Smith| 0.42 | 7 | 2
Gale | 0.21 | 6 | 2
Larry| 0.13 | 2 | 2

我提出的查询如下：

;WITH Filtered AS (
    SELECT *
        ,ROW_NUMBER() OVER (
            PARTITION BY [UserName] ORDER BY [Score]  DESC, [Position] --get unique UserNames getting the row with highest score and lowest position
            ) AS RowN
    FROM [dbo].[MyTable]
    WHERE [Class] = 2
)
SELECT *
FROM Filtered
WHERE RowN = 1
ORDER BY [Score] DESC, [Position] -- sort final set based on Score and Position

问题是，是否存在一种方法可以在没有 CTE（或子查询或临时表或结果的任何持有者）的情况下完成相同的操作，并且最有可能在单个 select 语句中完成？

T-SQL用于所述查询。

标签： sqlsql-servertsql

解决方案

使用MAX()和FIRST_VALUE()窗口功能：

SELECT DISTINCT 
       Username,
       MAX(Score) OVER (PARTITION BY Username) Score,
       FIRST_VALUE(Position) OVER (PARTITION BY Username ORDER BY Score DESC, Position) Position,
       Class
FROM MyTable       
WHERE Class = 2
ORDER BY Score DESC, Position

请参阅演示。

推荐阅读