如何让含UNION与ORDER BY的SQL查询结果正确显示？

核心问题 #

1. 重复分割线：第二个UNION从PRODUCT表查询，会生成与表中行数一致的分割线行，而非预期的单行分割线。
2. 排序逻辑错误：Inventory_value是带$符号的格式化字符串，按字符串排序与数值排序逻辑不符，导致结果顺序混乱。
3. 总计行位置失控：总计行参与全局排序，无法固定在结果末尾。

修正后的SQL语句 #

WITH product_data AS (
    SELECT 
        printf(' ' || P_CODE) AS "  P_CODE",
        printf(P_DESCRIPT) AS "      Product_Name",
        printf("%7.0f", P_QOH) AS "   P_QOH",
        printf("   $%.2f", P_PRICE) AS " P_PRICE",
        printf("       $%.2f", (P_QOH*P_PRICE)) AS "Inventory_value",
        -- 保留原始数值用于正确排序
        (P_QOH*P_PRICE) AS sort_value,
        -- 排序标识：产品行优先级最高
        1 AS sort_order
    FROM PRODUCT
),
separator AS (
    -- 生成单行分割线，排序标识次之
    SELECT 
        null AS "  P_CODE",
        null AS "      Product_Name",
        null AS "   P_QOH",
        null AS " P_PRICE",
        printf("---------------") AS "Inventory_value",
        0 AS sort_value,
        2 AS sort_order
),
total_row AS (
    -- 生成总计行，排序标识最低（固定在最后）
    SELECT 
        null AS "  P_CODE",
        null AS "      Product_Name",
        null AS "   P_QOH",
        "  Total:" AS " P_PRICE",
        printf("       $%.2f", SUM(P_QOH*P_PRICE)) AS "Inventory_value",
        0 AS sort_value,
        3 AS sort_order
    FROM PRODUCT
)
SELECT "  P_CODE", "      Product_Name", "   P_QOH", " P_PRICE", "Inventory_value"
FROM (
    SELECT * FROM product_data
    UNION ALL
    SELECT * FROM separator
    UNION ALL
    SELECT * FROM total_row
)
ORDER BY sort_order, sort_value DESC;

关键改动说明 #

• 替换UNION为UNION ALL：避免自动去重的额外开销，同时保证所有行完整保留（无需过滤重复）。
• 新增排序辅助列：
  • sort_order：控制整体输出顺序，产品行→分割线→总计行，确保总计行固定在末尾。
  • sort_value：使用原始数值型库存价值排序，彻底解决字符串排序的逻辑错误。
• 分割线改为单行：单独定义分割线逻辑，不再依赖PRODUCT表行数，确保只生成一行分割线。
• CTE结构拆分：用公共表表达式拆分各部分逻辑，提升SQL可读性与可维护性。

兼容老版本数据库的写法 #

如果你的SQL环境不支持CTE，可以改用子查询嵌套：

SELECT "  P_CODE", "      Product_Name", "   P_QOH", " P_PRICE", "Inventory_value"
FROM (
    SELECT 
        printf(' ' || P_CODE) AS "  P_CODE",
        printf(P_DESCRIPT) AS "      Product_Name",
        printf("%7.0f", P_QOH) AS "   P_QOH",
        printf("   $%.2f", P_PRICE) AS " P_PRICE",
        printf("       $%.2f", (P_QOH*P_PRICE)) AS "Inventory_value",
        (P_QOH*P_PRICE) AS sort_value,
        1 AS sort_order
    FROM PRODUCT
    
    UNION ALL
    
    SELECT 
        null, null, null, null, printf("---------------"), 0, 2
    
    UNION ALL
    
    SELECT 
        null, null, null, "  Total:", printf("       $%.2f", SUM(P_QOH*P_PRICE)), 0, 3
    FROM PRODUCT
)
ORDER BY sort_order, sort_value DESC;

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