SQL示例：数据分组与去重-实现分组后取单条记录的技巧

以下为示例表结构，业务场景的操作均以此表进行测试

示例：数据表结构

1、取每个id，两列字段值均较高的一行记录

思路：按照列字段grade,grade2的数值大小进行从高到低排序, 针对每一个主键id取出排序后的第一行数据。
下面介绍两种实现方式：

方法一：先order by +limit 再group by

group by更多的是和聚合函数一起使用，达到分组统计的效果，或者和having搭配使用对分组后的结果进行条件筛选，这里使用group by不是进行计数，而是利用了抽取第一行数据的特性。对排序后的结果进行group by 恰恰抽取到了想要grade,grade2两列值均最高的一行数据

代码示例：

select a.* from
(select * from test
order by grade desc, grade2 desc) a 
group by a.id 

运行结果：

​发现：id为肖战，取出来的数据不对，应该是体育的一行。所以，order by 未起到作用，实际上只是group by 抽取了第一行数据；
查了下网上的解决办法，可以通过在order by 后加limit语句的方式来解决。

select a.* from
(select * from test
order by grade desc, grade2 desc limit 1000) a 
group by a.id 

以上方法可行。

方法二：使用row_number函数，对分组排序后的数据增加行索引

SELECT *
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY id ORDER BY grade DESC,grade2 desc) AS row_num
    FROM test
) AS ranked
WHERE row_num = 1;

子查询结果：

执行结果：

2、取每个id，两列字段值总和最高的一行记录

WITH RankedScores AS (  -- 定义一个公用表表达式(CTE)名为RankedScores
    SELECT 
        id,  -- 选择id列
        subject1,  -- 选择subject1列
        grade,  -- 选择grade1列
        grade2,  -- 选择grade2列
        (grade + grade2) AS total_grade,  -- 计算grade1和grade2的总和，并将其命名为total_grade
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY (grade + grade2) DESC) AS rn  -- 使用窗口函数ROW_NUMBER()为每个id按照total_grade降序排序生成一个行号rn
    FROM 
        A  -- 从表A中选择数据
)
SELECT 
    id,  -- 选择id列
    subject1,  -- 选择subject1列
    grade1,  -- 选择grade1列
    grade2,  -- 选择grade2列
    total_grade  -- 选择计算得到的total_grade列
FROM 
    RankedScores  -- 从公用表表达式RankedScores中选择数据
WHERE 
    rn = 1;  -- 只选择行号rn为1的行，即每个id中total_grade最高的那一行

3、取每个科目，两次得分均最高的一行记录

SELECT 
    A.*
FROM 
    (
        SELECT 
            A.*,
            ROW_NUMBER() OVER (PARTITION BY subject1 ORDER BY grade1 DESC, grade2 DESC) AS rn
        FROM 
            A
    ) AS RankedScores
WHERE 
    rn = 1;

4、取每个科目，两列字段值总和最高的一行记录

SELECT 
    A.*
FROM 
    (
        SELECT 
            A.*,
            ROW_NUMBER() OVER (PARTITION BY subject1 ORDER BY (grade1 + grade2) DESC) AS rn
        FROM 
            A
    ) AS RankedScores
WHERE 
    rn = 1;

处理方式一样，把按成员分组改成按学科即可，表A中数据，偶尔可能不满足两个字段值均最高的数据（暂不纠结啦），主要考虑实现方式。

注：如计算字段为文本，请先进行字符类型的转换，参考以下代码：

SELECT 
    A.*
FROM 
    (
        SELECT 
            A.*,
            TRY_CONVERT(FLOAT, grade1) AS grade1_numeric,
            TRY_CONVERT(FLOAT, grade2) AS grade2_numeric,
            ROW_NUMBER() OVER (PARTITION BY subject1 ORDER BY (TRY_CONVERT(FLOAT, grade1) + TRY_CONVERT(FLOAT, grade2)) DESC) AS rn
        FROM 
            A
        WHERE 
            TRY_CONVERT(FLOAT, grade1) IS NOT NULL AND 
            TRY_CONVERT(FLOAT, grade2) IS NOT NULL
    ) AS RankedScores
WHERE 
    rn = 1;