解决MySQL错误#1222：SELECT语句列数不一致问题

首先直接点出问题根源：UNION ALL要求所有拼接的SELECT语句返回完全相同数量的列，而且对应位置的列数据类型要兼容。你现在第一个子查询返回了8列（sale_id、date、amt、sgst、cgst、igst、i_sc、total），但第二个子查询只返回了1列（total_discount），这就导致数据库抛出#1222 - The used SELECT statements have a different number of columns错误。

正确的修改方案 #

我们需要让第二个子查询补全和第一个子查询一致的列数，对于不需要的列用NULL填充，同时保证两个子查询的分组维度（按日期天）完全对齐，这样拼接后的结果才合理。修改后的SQL如下：

SELECT * FROM (
    -- 第一个子查询：统计每日商品的各项金额
    SELECT 
        sales.id as sale_id,
        DATE_FORMAT(sales.date, '%e') AS date,
        SUM(line.quantity * line.real_unit_price) AS amt,
        SUM(line.food_sgst + line.liquor_sgst) AS sgst,
        SUM(line.food_cgst + line.liquor_cgst) AS cgst,
        SUM(line.food_igst + line.liquor_igst) AS igst,
        SUM(line.item_sc) AS i_sc,
        SUM(
            (line.quantity * line.real_unit_price) +
            (line.food_sgst + line.liquor_sgst) +
            (line.food_cgst + line.liquor_cgst) +
            (line.food_igst + line.liquor_igst) +
            line.item_sc
        ) AS total,
        NULL AS total_discount -- 新增空列，对应第二个子查询的折扣列
    FROM sale_items as line 
    LEFT JOIN sales as sales ON line.sale_id = sales.id 
    WHERE created_by = 17 AND DATE_FORMAT(sales.date, '%Y-%m') = '2019-02' 
    GROUP BY DATE_FORMAT(sales.date, '%e')

    UNION ALL

    -- 第二个子查询：统计每日总折扣，补全和第一个子查询相同的列
    SELECT 
        NULL AS sale_id, -- 补全空列
        DATE_FORMAT(a.date, '%e') AS date,
        NULL AS amt, -- 补全空列
        NULL AS sgst, -- 补全空列
        NULL AS cgst, -- 补全空列
        NULL AS igst, -- 补全空列
        NULL AS i_sc, -- 补全空列
        NULL AS total, -- 补全空列
        SUM(a.total_discount) AS total_discount -- 折扣列
    FROM sales a 
    WHERE DATE_FORMAT(a.date, '%Y-%m') = '2019-02' 
    GROUP BY DATE_FORMAT(a.date, '%e')
) as salesall
ORDER BY date; -- 可选：按日期排序结果

关键改动说明 #

• 补全列数：在第一个子查询中新增NULL AS total_discount，在第二个子查询中补全前8个对应列的NULL值，确保两个子查询都返回9列。
• 对齐分组维度：两个子查询都按DATE_FORMAT(date, '%e')（日期的天）分组，保证拼接的是同一日期的统计数据。
• 修正最初的折扣统计问题：去掉了错误的SUM(DISTINCT total_discount)，改为直接按日期汇总所有账单的折扣，这样就能得到正确的每日总折扣（比如你提到的10+10+15+10=45的场景，会按日期正确累加）。

额外优化建议 #

如果你希望把每日的商品统计和折扣统计合并到同一行（而不是分成两行），其实不需要用UNION ALL，可以直接在主查询中关联计算每日折扣，结果更简洁：

SELECT 
    DATE_FORMAT(sales.date, '%e') AS date,
    SUM(line.quantity * line.real_unit_price) AS amt,
    SUM(line.food_sgst + line.liquor_sgst) AS sgst,
    SUM(line.food_cgst + line.liquor_cgst) AS cgst,
    SUM(line.food_igst + line.liquor_igst) AS igst,
    SUM(line.item_sc) AS i_sc,
    SUM(
        (line.quantity * line.real_unit_price) +
        (line.food_sgst + line.liquor_sgst) +
        (line.food_cgst + line.liquor_cgst) +
        (line.food_igst + line.liquor_igst) +
        line.item_sc
    ) AS total,
    (SELECT SUM(total_discount) FROM sales a WHERE DATE_FORMAT(a.date, '%Y-%m') = '2019-02' AND DATE_FORMAT(a.date, '%e') = DATE_FORMAT(sales.date, '%e')) AS total_discount
FROM sale_items as line 
LEFT JOIN sales as sales ON line.sale_id = sales.id 
WHERE created_by = 17 AND DATE_FORMAT(sales.date, '%Y-%m') = '2019-02' 
GROUP BY DATE_FORMAT(sales.date, '%e');

这个版本会把每日的商品统计和总折扣放在同一行，避免了UNION ALL带来的行拆分问题，查询逻辑也更清晰。

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