SQL Azure使用UNION/UNION ALL合并多表查询报205错误如何解决

使用UNION、INTERSECT或EXCEPT运算符合并的所有查询，其目标列表中的表达式数量必须相等。
你触发报错的核心原因是三个UNION ALL连接的SELECT语句返回的列数不匹配：

• 第一个关联Cut_job的查询返回6列：Job_no、type_of_machine_use、amount_of_time_machine_use、martirial_used、labor_time、Print_out
• 第二个关联Paint_job的查询返回5列：Job_no、Color、Volume、labor_time、Print_out
• 第三个关联Fit_job的查询返回3列：Job_no、labor_time、Print_out

根据你最终的输出需求选择对应方案：

方案1：仅保留需要输出的字段（推荐，性能最优） #

你最终只需要输出Print_out字段，可直接将三个UNION ALL分支的查询字段统一为仅查询该字段，修改后的CTE部分代码如下：

With q as(
    SELECT Cut_job.date_job_completed as Print_out
    FROM Cut_job
    INNER JOIN Job ON Job.Job_no = Cut_job.Job_no
    INNER JOIN No_of_Process ON No_of_Process.Process_id = Job.Process_id
    INNER JOIN Department ON Department.Department_number = No_of_Process.Department_number
    WHERE Cut_job.date_job_completed = @date_job_completed AND Department.Department_number = @Department_number
    
    UNION ALL

    SELECT Paint_job.date_job_completed as Print_out
    FROM Paint_job
    INNER JOIN Job ON Job.Job_no = Paint_job.Job_no
    INNER JOIN No_of_Process ON No_of_Process.Process_id = Job.Process_id
    INNER JOIN Department ON Department.Department_number = No_of_Process.Department_number
    WHERE Paint_job.date_job_completed = @date_job_completed AND Department.Department_number = @Department_number
    
    UNION ALL

    SELECT Fit_job.date_job_completed as Print_out
    FROM Fit_job
    INNER JOIN Job ON Job.Job_no = Fit_job.Job_no
    INNER JOIN No_of_Process ON No_of_Process.Process_id = Job.Process_id
    INNER JOIN Department ON Department.Department_number = No_of_Process.Department_number
    WHERE Fit_job.date_job_completed = @date_job_completed AND Department.Department_number = @Department_number
) 
SELECT Print_out
FROM q

方案2：保留所有业务字段，补空位对齐 #

如果需要保留不同作业类型的独有字段，给缺少对应字段的查询分支用NULL或者默认值占位，保证所有分支的列数、对应位置的字段类型一致即可，示例如下：

With q as(
    SELECT 
        Job.Job_no, 
        type_of_machine_use, 
        amount_of_time_machine_use, 
        martirial_used, 
        labor_time, 
        date_job_completed as Print_out
    FROM Cut_job
    -- 关联条件、筛选条件和原代码一致
    UNION ALL

    SELECT 
        Job.Job_no, 
        Color as type_of_machine_use, -- 类型兼容可替换，也可填NULL
        Volume as amount_of_time_machine_use, -- 同上
        NULL as martirial_used, -- 无对应字段补NULL
        labor_time, 
        date_job_completed as Print_out
    FROM Paint_job
    -- 关联条件、筛选条件和原代码一致
    UNION ALL

    SELECT 
        Job.Job_no, 
        NULL as type_of_machine_use,
        NULL as amount_of_time_machine_use,
        NULL as martirial_used,
        labor_time, 
        date_job_completed as Print_out
    FROM Fit_job
    -- 关联条件、筛选条件和原代码一致
) 
SELECT * FROM q -- 可自定义输出字段

• 存储过程的日期参数@date_job_completed建议从varchar(50)改为DATE/DATETIME类型，避免字符串格式不匹配导致的筛选错误
• 你测试时传入的参数值'23/23/2019'为非法日期，不存在23月，测试时请替换为合法日期值

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