如何合并SQL订单明细查询与统计记录查询的结果？

你无法用UNION ALL合并两个查询的核心原因是列数不匹配（第一个查询返回9列，第二个仅返回7列），且部分列的数据类型不一致。要解决这个问题，需要调整统计查询的结构，让它的列数、列别名和数据类型与主查询对齐，再通过UNION ALL合并。

调整后的完整SQL代码 #

-- 主查询：获取订单明细数据
SELECT 
    HHIITMN AS "Product Number", 
    SUBSTR(HHIDTES,5,2) || SUBSTR(HHIDTES,7,2) || SUBSTR(HHIDTES,1,4) AS "Transaction Date",
    CASE HHIBCCD WHEN 'Y' THEN CAST(ROUND(((HHIQYSA/HHIPCKI)*100),0) AS DECIMAL(12,0))
        ELSE CAST(ROUND((HHIQYSA*100),0) AS DECIMAL(12,0))
    END AS "Quantity",
    CAST(ROUND((HHIPRCN*100),0) AS DECIMAL(12,0)) AS "Unit Price",
    CAST(ROUND((HHIQYSA*100),0) * ROUND((HHIPRCN*100),0) AS DECIMAL(12,0)) AS "Total Price",
    '1' AS "Distributor Number", 
    HHICUSN AS "Operator Number",
    CASE HHICWCD WHEN 'Y' THEN CAST(ROUND((HHIEXCW*100),0) AS DECIMAL(15,0)) ELSE 0 END AS "Weight Shipped",
    CASE
        WHEN HHICWCD = 'Y' THEN 'LB'
        WHEN HHIBCCD = 'Y' THEN 'EA'
        ELSE 'CS'
    END AS "Item Unit Of Measure"
FROM S2151BDW.PWRDTA.HHIORDDP 
WHERE HHICMPN = '  1' 
    AND HHIINVN IN ('3610036', '3610037', '3610038')

UNION ALL

-- 统计行查询：生成包含记录数和唯一操作方的统计行
SELECT 
    '1234' AS "Product Number",
    SUBSTR(DEC(DATE(CURRENT_DATE)),5,2) || SUBSTR(DEC(DATE(CURRENT_DATE)),7,2) || SUBSTR(DEC(DATE(CURRENT_DATE)),1,4) AS "Transaction Date",
    CONCAT('RECORDCOUNT=', COUNT(*), ', HOUSECOUNT=', COUNT(DISTINCT HHICUSN), ' v2.0') AS "Quantity",
    NULL AS "Unit Price",
    NULL AS "Total Price",
    NULL AS "Distributor Number",
    NULL AS "Operator Number",
    NULL AS "Weight Shipped",
    NULL AS "Item Unit Of Measure"
FROM S2151BDW.PWRDTA.HHIORDDP 
WHERE HHICMPN = '  1' 
    AND HHIINVN IN ('3610036', '3610037', '3610038')

-- 排序规则：让统计行排在所有明细行之后
ORDER BY 
    CASE WHEN "Product Number" = '1234' THEN 1 ELSE 0 END,
    "Transaction Date", "Operator Number", "Product Number"

关键说明 #

1. 列对齐：统计查询新增了2列空值占位符，确保总列数与主查询一致，同时所有列的别名与主查询完全匹配，数据类型尽量兼容（统计文本放在Quantity列，数据库会自动处理数值与字符串的兼容）。
2. 排序优化：通过CASE表达式设置排序优先级，让标记为1234的统计行排在最后，避免穿插在明细数据中。
3. 可维护性优化：如果不想重复编写WHERE条件，可以用CTE（公共表表达式）封装主查询逻辑，再基于CTE生成明细和统计行，示例如下：

WITH OrderDetails AS (
    SELECT 
        HHIITMN AS "Product Number", 
        SUBSTR(HHIDTES,5,2) || SUBSTR(HHIDTES,7,2) || SUBSTR(HHIDTES,1,4) AS "Transaction Date",
        CASE HHIBCCD WHEN 'Y' THEN CAST(ROUND(((HHIQYSA/HHIPCKI)*100),0) AS DECIMAL(12,0))
            ELSE CAST(ROUND((HHIQYSA*100),0) AS DECIMAL(12,0))
        END AS "Quantity",
        CAST(ROUND((HHIPRCN*100),0) AS DECIMAL(12,0)) AS "Unit Price",
        CAST(ROUND((HHIQYSA*100),0) * ROUND((HHIPRCN*100),0) AS DECIMAL(12,0)) AS "Total Price",
        '1' AS "Distributor Number", 
        HHICUSN AS "Operator Number",
        CASE HHICWCD WHEN 'Y' THEN CAST(ROUND((HHIEXCW*100),0) AS DECIMAL(15,0)) ELSE 0 END AS "Weight Shipped",
        CASE
            WHEN HHICWCD = 'Y' THEN 'LB'
            WHEN HHIBCCD = 'Y' THEN 'EA'
            ELSE 'CS'
        END AS "Item Unit Of Measure",
        HHICUSN -- 保留用于统计唯一操作方数量
    FROM S2151BDW.PWRDTA.HHIORDDP 
    WHERE HHICMPN = '  1' 
        AND HHIINVN IN ('3610036', '3610037', '3610038')
)
-- 选择明细行
SELECT * FROM OrderDetails
UNION ALL
-- 生成统计行
SELECT 
    '1234' AS "Product Number",
    SUBSTR(DEC(DATE(CURRENT_DATE)),5,2) || SUBSTR(DEC(DATE(CURRENT_DATE)),7,2) || SUBSTR(DEC(DATE(CURRENT_DATE)),1,4) AS "Transaction Date",
    CONCAT('RECORDCOUNT=', COUNT(*), ', HOUSECOUNT=', COUNT(DISTINCT HHICUSN), ' v2.0') AS "Quantity",
    NULL AS "Unit Price",
    NULL AS "Total Price",
    NULL AS "Distributor Number",
    NULL AS "Operator Number",
    NULL AS "Weight Shipped",
    NULL AS "Item Unit Of Measure"
FROM OrderDetails
ORDER BY 
    CASE WHEN "Product Number" = '1234' THEN 1 ELSE 0 END,
    "Transaction Date", "Operator Number", "Product Number"

内容的提问来源于stack exchange，提问作者Dan B.