如何基于已有查询生成含全颜色（无数据显0）的主SQL查询？

场景1：按颜色分组统计总数量 #

如果需要展示每种颜色的总数量（不区分尺寸），同时返回color、size（聚合该颜色所有尺寸）、number、is_chewy字段，可使用以下SQL：

WITH all_colors AS (
    -- 列出全部10种目标颜色及对应的is_chewy属性（根据实际业务定义）
    SELECT '红色' AS color, '是' AS is_chewy
    UNION ALL SELECT '蓝色', '是'
    UNION ALL SELECT '黄色', '否'
    UNION ALL SELECT '紫色', '否'
    UNION ALL SELECT '绿色', '是'
    UNION ALL SELECT '橙色', '否'
    UNION ALL SELECT '粉色', '是'
    UNION ALL SELECT '黑色', '否'
    UNION ALL SELECT '白色', '是'
    UNION ALL SELECT '棕色', '否'
),
color_stats AS (
    -- 对现有数据按颜色分组统计
    SELECT 
        color,
        COUNT(*) AS number,
        STRING_AGG(DISTINCT size, ', ') AS size -- 用逗号拼接该颜色所有尺寸
    FROM (
        -- 替换为你现有的返回color和size的查询语句
        SELECT color, size FROM gum_data
    ) existing_query
    GROUP BY color
)
SELECT 
    ac.color,
    COALESCE(cs.size, '') AS size,
    COALESCE(cs.number, 0) AS number,
    ac.is_chewy
FROM all_colors ac
LEFT JOIN color_stats cs ON ac.color = cs.color
ORDER BY ac.color;

逻辑说明：

1. 动态生成全量颜色列表：通过UNION ALL定义所有10种颜色和is_chewy属性，无需创建永久表或视图，每次查询都会动态生成。
2. 统计现有数据：对已有查询结果按颜色分组，统计数量并聚合尺寸信息。
3. 左连接补0：将全量颜色列表与统计结果左连接，用COALESCE把无数据的number转为0，size转为空字符串。

场景2：按颜色+尺寸组合统计数量 #

如果需要展示每种颜色的每个尺寸的数量（无对应数据则显示0），可使用以下SQL：

WITH all_colors AS (
    -- 列出全部10种目标颜色及对应的is_chewy属性
    SELECT '红色' AS color, '是' AS is_chewy
    UNION ALL SELECT '蓝色', '是'
    UNION ALL SELECT '黄色', '否'
    UNION ALL SELECT '紫色', '否'
    UNION ALL SELECT '绿色', '是'
    UNION ALL SELECT '橙色', '否'
    UNION ALL SELECT '粉色', '是'
    UNION ALL SELECT '黑色', '否'
    UNION ALL SELECT '白色', '是'
    UNION ALL SELECT '棕色', '否'
),
all_sizes AS (
    -- 列出所有可能的尺寸值
    SELECT '小' AS size
    UNION ALL SELECT '中'
    UNION ALL SELECT '大'
),
all_combinations AS (
    -- 生成颜色与尺寸的所有组合
    SELECT ac.color, ac.is_chewy, asz.size
    FROM all_colors ac
    CROSS JOIN all_sizes asz
),
existing_data AS (
    -- 替换为你现有的返回color和size的查询语句
    SELECT color, size FROM gum_data
)
SELECT 
    ac.color,
    ac.size,
    COUNT(ed.color) AS number, -- 无匹配数据时自动返回0
    ac.is_chewy
FROM all_combinations ac
LEFT JOIN existing_data ed ON ac.color = ed.color AND ac.size = ed.size
GROUP BY ac.color, ac.size, ac.is_chewy
ORDER BY ac.color, ac.size;

逻辑说明：

1. 生成颜色+尺寸的全量组合：通过CROSS JOIN生成所有颜色与尺寸的笛卡尔积，确保所有可能的组合都被覆盖。
2. 左连接现有数据：将全量组合与现有数据关联，按组合分组统计数量，无匹配数据时COUNT会自动返回0，无需额外处理。

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