如何结合DISTINCT与MAX条件实现PRV按AU分组统计？

现有数据库表（PRV字段存在重复值）：

原查询用于按AU分组统计不同PRV的数量：

SELECT
    COUNT(*),
    AU
FROM
    (
        SELECT DISTINCT
            PRV,
            AU
        FROM
            tablename
        WHERE
            MV > 0
    ) AS K
GROUP BY
    AU

原查询结果：

需求说明 #

分组统计需满足两个条件：

• 仅统计MV>0的记录
• 对于重复的PRV，取MV最大的那条记录对应的AU进行统计（比如PRV='G-112'的两条记录，MV最大值是401，对应AU=LA，不应将LC计入统计）

期望结果：

用户已知MAX关键字，但不清楚如何应用到当前场景。

方法一：使用窗口函数（推荐） #

通过ROW_NUMBER()窗口函数给每个PRV分组内的记录按MV降序排序，取排序为1的那条（即MV最大的记录），再基于该结果统计：

SELECT
    COUNT(t.PRV) AS `COUNT(*)`,
    t.AU
FROM
    (
        SELECT
            PRV,
            AU,
            MV,
            -- 按PRV分组，MV降序排序，每条PRV保留MV最大的第一条
            ROW_NUMBER() OVER (PARTITION BY PRV ORDER BY MV DESC) AS rn
        FROM
            tablename
        WHERE
            MV > 0 -- 先过滤MV>0的记录
    ) t
WHERE
    t.rn = 1 -- 仅保留每个PRV中MV最大的记录
GROUP BY
    t.AU

方法二：使用关联子查询 #

如果数据库不支持窗口函数，可通过关联子查询找到每个PRV对应的最大MV记录：

SELECT
    COUNT(DISTINCT t1.PRV) AS `COUNT(*)`,
    t1.AU
FROM
    tablename t1
WHERE
    t1.MV > 0
    -- 筛选出当前PRV下MV等于最大值的记录
    AND t1.MV = (SELECT MAX(MV) FROM tablename t2 WHERE t2.PRV = t1.PRV)
GROUP BY
    t1.AU

逻辑说明 #

两种方法核心逻辑一致：

1. 先过滤掉MV=0的无效记录
2. 为每个PRV仅保留MV最大的那条记录
3. 按AU分组统计符合条件的PRV数量

内容的提问来源于stack exchange，提问作者Lyon Möore