如何编写SQL查询每张发票对应关联订单数最多的买家

问题原因 #

你现有SQL的逻辑完全没有实现筛选逻辑：内层子查询已经按invoice_N（发票编号）、buyer（买家）分组，计算出了每张发票下每个买家对应的关联订单数，外层再次按同样的两个字段分组取最大值，等价于直接返回内层的全部计算结果，不会筛选出每张发票对应的订单数最多的买家。

解决方案 #

方案1：窗口函数版本（适配MySQL 8.0+、PostgreSQL、SQL Server等所有支持窗口函数的主流数据库，推荐使用）

SELECT invoice_N, buyer, count_order
FROM (
    SELECT
        invoice_N,
        buyer,
        COUNT(DISTINCT OrderIdent) AS count_order,
        -- 按发票编号分组，组内按关联订单数倒序排名
        RANK() OVER(PARTITION BY invoice_N ORDER BY COUNT(DISTINCT OrderIdent) DESC) AS rk
    FROM invoices
    LEFT JOIN orders ON -- 此处补全你原本的表关联条件
    GROUP BY invoice_N, buyer
) t
WHERE rk = 1;

如果同一张发票下存在多个买家并列订单数最多的情况，上述语句会返回所有并列第一的买家；如果仅需要取任意一个最高订单数的买家，把RANK()替换为ROW_NUMBER()即可。

方案2：兼容老版本无窗口函数的数据库（如MySQL 5.x）

SELECT a.invoice_N, a.buyer, a.count_order
FROM (
    SELECT
        invoice_N,
        buyer,
        COUNT(DISTINCT OrderIdent) AS count_order
    FROM invoices
    LEFT JOIN orders ON -- 此处补全你原本的表关联条件
    GROUP BY invoice_N, buyer
) a
INNER JOIN (
    -- 先计算每张发票对应的最高关联订单数
    SELECT invoice_N, MAX(count_order) AS max_order_count
    FROM (
        SELECT
            invoice_N,
            COUNT(DISTINCT OrderIdent) AS count_order
        FROM invoices
        LEFT JOIN orders ON -- 此处补全你原本的表关联条件
        GROUP BY invoice_N, buyer
    ) t
    GROUP BY invoice_N
) b ON a.invoice_N = b.invoice_N AND a.count_order = b.max_order_count;

内容的提问来源于stack exchange，提问作者CCC