SQL使用COUNT计算时保留全部记录的分组查询问题求助

你的问题核心在于：你需要保留所有关联展开的行，但同时要让每个row_sid下的所有行的amount和qty，除以该row_sid对应的总行数。GROUP BY之所以会丢失行，是因为它的作用是聚合分组，把同一组的行合并成一行，这和你要保留所有行的需求矛盾。

为什么之前的尝试有问题？ #

当你用GROUP BY row_sid时，虽然能计算出每个row_sid的行数，但聚合操作会把该row_sid下的多行合并成一行，自然就丢失了你需要的展开后的产品明细行。而如果去掉GROUP BY，你没办法直接获取每个row_sid对应的总行数来做除法，所以计算结果不对。

正确的解决方案：用窗口函数或子查询获取每个row_sid的行数 #

这里有两种可行的方法，根据你的MySQL版本选择：

方法1：使用窗口函数（MySQL 8.0及以上版本推荐）

窗口函数COUNT() OVER(PARTITION BY row_sid)可以在不聚合行的前提下，计算出每个row_sid对应的总行数。直接把这个值作为除数即可：

SELECT 
    s.row_sid,
    p.product_description,
    -- 均分amount，可通过ROUND()调整小数精度
    s.amount / COUNT(*) OVER(PARTITION BY s.row_sid) AS divided_amount,
    -- 均分qty
    s.qty / COUNT(*) OVER(PARTITION BY s.row_sid) AS divided_qty
FROM summary s
INNER JOIN list_header lh ON s.product_list_sid = lh.product_list_sid
INNER JOIN list_detail ld ON lh.product_list_sid = ld.product_list_sid
INNER JOIN product p ON ld.product_sid = p.product_sid;

方法2：使用子查询预计算行数（兼容MySQL 5.x版本）

如果你的MySQL版本不支持窗口函数，可以先通过子查询计算每个row_sid对应的总行数，再关联到原始查询中：

SELECT 
    s.row_sid,
    p.product_description,
    s.amount / row_count AS divided_amount,
    s.qty / row_count AS divided_qty
FROM summary s
-- 关联预计算的行数表
INNER JOIN (
    SELECT 
        s_inner.row_sid,
        COUNT(*) AS row_count
    FROM summary s_inner
    INNER JOIN list_header lh ON s_inner.product_list_sid = lh.product_list_sid
    INNER JOIN list_detail ld ON lh.product_list_sid = ld.product_list_sid
    GROUP BY s_inner.row_sid
) rc ON s.row_sid = rc.row_sid
-- 原来的关联逻辑不变
INNER JOIN list_header lh ON s.product_list_sid = lh.product_list_sid
INNER JOIN list_detail ld ON lh.product_list_sid = ld.product_list_sid
INNER JOIN product p ON ld.product_sid = p.product_sid;

结果验证 #

以你的测试数据为例：

• row_sid=1和2对应的关联行数是3（product_list_sid=1的list_detail有3条）
• row_sid=3对应的关联行数是4（product_list_sid=2的list_detail有4条）

修改后的查询会保留所有10行（row_sid1有3行，row_sid2有3行，row_sid3有4行），并且：

• row_sid1的divided_amount=3/3=1，divided_qty=9/3=3
• row_sid2的divided_amount=15/3=5，divided_qty=45/3=15
• row_sid3的divided_amount=12/4=3，divided_qty=36/4=9

完全符合你的需求：保留所有原行，同时计算正确的均分后的值。

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