如何使用UNION ALL连接两个统计工作日的SQL查询？

直接将两个查询通过UNION ALL拼接即可，需保证两个查询返回的列数、数据类型完全匹配，同时将排序逻辑调整到整体语句末尾。以下是完整的SQL代码：

WITH week_source AS (
    SELECT week_desc,
           TO_CHAR(day_date, 'IYYY') || 'W' || TO_CHAR(day_date, 'IW') AS week,
           (SELECT DISTINCT day_number
              FROM period_day
             WHERE day_key = d.day_key 
               AND day_number NOT IN ('H', 'W') 
               AND WEEK_NUM != TO_CHAR(SYSDATE, 'WW')) AS workdays_count
      FROM period_day d
     WHERE TO_CHAR(day_date, 'IYYYIW') BETWEEN TO_CHAR((SYSDATE - 1) - 28, 'IYYYIW')
                                           AND TO_CHAR((SYSDATE - 1), 'IYYYIW')
)
SELECT DISTINCT week_desc, week, workdays_count
  FROM week_source
 WHERE workdays_count IS NOT NULL
UNION ALL
SELECT 'W' || TO_CHAR(SYSDATE - 1, 'IW') AS WEEK_DESC,
       TO_CHAR(SYSDATE - 1, 'IYYYIW') AS WEEK,
       COUNT(day_date) AS WORKDAYS_COUNT
  FROM period_day
 WHERE day_number NOT IN ('H', 'W')
   AND TO_CHAR(day_date, 'IYYYIW') = TO_CHAR((SYSDATE - 1), 'IYYYIW')
   AND day_date <= (SYSDATE - 1)
ORDER BY week;

关键注意事项 #

• UNION ALL强制要求两个子查询返回的列数、数据类型完全一致，这里两个查询均返回week_desc、week、workdays_count三列，类型匹配
• 移除了第一个查询内部的ORDER BY，改为在整个UNION ALL结果后统一排序，避免排序逻辑重复且保证结果整体有序
• UNION ALL会保留所有重复行，若需要自动去重可替换为UNION，按需选择即可

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