嘿，这个需求其实挺常见的，你可以用两种方式实现：一种是用UNION ALL把分组统计结果和全量统计结果拼接起来，另一种是用更简洁的ROLLUP语法（如果你的数据库支持的话）。我给你详细拆解一下：

方案一：用UNION ALL手动拼接结果 #

这种方法兼容性最好，不管什么数据库基本都支持。核心思路就是先算出每个购物者的占比，再单独算出所有购物者的整体占比，最后把两个结果集拼在一起：

WITH shopper_visits AS (
    -- 这里替换成你实际的子查询
    SELECT 'Bill' AS Shopper, 'A' AS Visit, 1 AS Butter, 0 AS Milk, 1 AS Eggs UNION ALL
    SELECT 'Bill' AS Shopper, 'B' AS Visit, 0 AS Butter, 1 AS Milk, 1 AS Eggs UNION ALL
    SELECT 'John' AS Shopper, 'C' AS Visit, 0 AS Butter, 1 AS Milk, 0 AS Eggs UNION ALL
    SELECT 'John' AS Shopper, 'D' AS Visit, 1 AS Butter, 1 AS Milk, 1 AS Eggs
)
-- 第一部分：单个购物者的占比统计
SELECT
    Shopper,
    CONCAT(ROUND(SUM(Butter) * 100 / COUNT(*), 0), '%') AS `Butter %`,
    CONCAT(ROUND(SUM(Milk) * 100 / COUNT(*), 0), '%') AS `Milk %`,
    CONCAT(ROUND(SUM(Eggs) * 100 / COUNT(*), 0), '%') AS `Eggs %`
FROM shopper_visits
GROUP BY Shopper
-- 第二部分：追加全量购物者的整体占比
UNION ALL
SELECT
    'All' AS Shopper,
    CONCAT(ROUND(SUM(Butter) * 100 / COUNT(*), 0), '%') AS `Butter %`,
    CONCAT(ROUND(SUM(Milk) * 100 / COUNT(*), 0), '%') AS `Milk %`,
    CONCAT(ROUND(SUM(Eggs) * 100 / COUNT(*), 0), '%') AS `Eggs %`
FROM shopper_visits
-- 确保"All"行排在最后
ORDER BY CASE WHEN Shopper = 'All' THEN 1 ELSE 0 END, Shopper;

方案二：用ROLLUP自动生成总计行 #

如果你的数据库支持GROUP BY WITH ROLLUP（比如MySQL 8.0+、PostgreSQL、SQL Server、Oracle等），这个方法更简洁，不需要重复写统计逻辑。ROLLUP会自动在分组结果的最后生成一行总计，我们只需要把总计行的Shopper字段替换成'All'即可：

WITH shopper_visits AS (
    -- 同样替换成你实际的子查询
    SELECT 'Bill' AS Shopper, 'A' AS Visit, 1 AS Butter, 0 AS Milk, 1 AS Eggs UNION ALL
    SELECT 'Bill' AS Shopper, 'B' AS Visit, 0 AS Butter, 1 AS Milk, 1 AS Eggs UNION ALL
    SELECT 'John' AS Shopper, 'C' AS Visit, 0 AS Butter, 1 AS Milk, 0 AS Eggs UNION ALL
    SELECT 'John' AS Shopper, 'D' AS Visit, 1 AS Butter, 1 AS Milk, 1 AS Eggs
)
SELECT
    -- 把ROLLUP生成的NULL替换成'All'
    COALESCE(Shopper, 'All') AS Shopper,
    CONCAT(ROUND(SUM(Butter) * 100 / COUNT(*), 0), '%') AS `Butter %`,
    CONCAT(ROUND(SUM(Milk) * 100 / COUNT(*), 0), '%') AS `Milk %`,
    CONCAT(ROUND(SUM(Eggs) * 100 / COUNT(*), 0), '%') AS `Eggs %`
FROM shopper_visits
GROUP BY Shopper WITH ROLLUP
-- 确保总计行排在最后
ORDER BY CASE WHEN Shopper IS NULL THEN 1 ELSE 0 END, Shopper;

小提示 #

• 如果你用的是老版本的数据库（比如MySQL 5.x），不支持WITH子句的话，可以把shopper_visits换成实际的子查询，或者临时表。
• 计算占比时的ROUND函数可以根据你的需求调整小数位数，比如保留1位小数就用ROUND(...,1)。

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