如何将存储过程中多条SELECT语句的输出结果合并为同一张表

解决方案 #

你当前每条SELECT返回的列结构完全一致，只需通过以下两种方式即可合并所有结果为单张表：

方法1：快速适配原有代码

在每两条相邻的SELECT语句之间添加UNION ALL关键字即可，示例如下：

SELECT (SELECT [DailyGoal] FROM [SQL_Testing].[dbo].[packing_goals] WHERE SelCode = 'IMF') as 'DailyGoal',(SELECT ISNULL(SUM(QtyPacked),0) FROM [MKE_PSS].[dbo].[vwPackingActivitySnapshotSumQty2] WHERE SelCode = 'IMF' AND Shift = '3' AND PackDate = @Date) as 'Shift3',(SELECT ISNULL(SUM(QtyPacked),0) FROM [MKE_PSS].[dbo].[vwPackingActivitySnapshotSumQty2] WHERE SelCode = 'IMF' AND Shift = '1' AND PackDate = @Date) as 'Shift1',(SELECT ISNULL(SUM(QtyPacked),0) FROM [MKE_PSS].[dbo].[vwPackingActivitySnapshotSumQty2] WHERE SelCode = 'IMF' AND Shift = '2' AND PackDate = @Date) as 'Shift2',(SELECT [SelCode] FROM [SQL_Testing].[dbo].[packing_goals] WHERE SelCode = 'IMF') as 'SelCode' 
UNION ALL
SELECT (SELECT [DailyGoal] FROM [SQL_Testing].[dbo].[packing_goals] WHERE SelCode = 'CRF') as 'DailyGoal',(SELECT ISNULL(SUM(QtyPacked),0) FROM [MKE_PSS].[dbo].[vwPackingActivitySnapshotSumQty2] WHERE SelCode = 'CRF' AND Shift = '3' AND PackDate = @Date) as 'Shift3',(SELECT ISNULL(SUM(QtyPacked),0) FROM [MKE_PSS].[dbo].[vwPackingActivitySnapshotSumQty2] WHERE SelCode = 'CRF' AND Shift = '1' AND PackDate = @Date) as 'Shift1',(SELECT ISNULL(SUM(QtyPacked),0) FROM [MKE_PSS].[dbo].[vwPackingActivitySnapshotSumQty2] WHERE SelCode = 'CRF' AND Shift = '2' AND PackDate = @Date) as 'Shift2',(SELECT [SelCode] FROM [SQL_Testing].[dbo].[packing_goals] WHERE SelCode = 'CRF') as 'SelCode'
-- 剩余所有SELECT语句都按上述规则用UNION ALL拼接即可

注意：无去重需求时优先使用UNION ALL，执行效率远高于自动去重的UNION。

方法2：优化写法（更推荐）

无需为每个SelCode重复写查询逻辑，通过表关联+条件聚合即可实现相同效果，代码更简洁、执行效率更高，后续新增SelCode也无需调整代码结构：

SELECT
    g.DailyGoal,
    ISNULL(SUM(CASE WHEN a.Shift = '3' THEN a.QtyPacked ELSE 0 END), 0) AS Shift3,
    ISNULL(SUM(CASE WHEN a.Shift = '1' THEN a.QtyPacked ELSE 0 END), 0) AS Shift1,
    ISNULL(SUM(CASE WHEN a.Shift = '2' THEN a.QtyPacked ELSE 0 END), 0) AS Shift2,
    g.SelCode
FROM [SQL_Testing].[dbo].[packing_goals] g
LEFT JOIN [MKE_PSS].[dbo].[vwPackingActivitySnapshotSumQty2] a
    ON g.SelCode = a.SelCode
    AND a.PackDate = @Date
-- 若需查询packing_goals表中所有SelCode的数据，直接删除下面的WHERE条件即可
WHERE g.SelCode IN ('IMF','CRF','DNF','TBF','DIF','MSF','QBF','IPF','FSF','XTP','XTF','XWF','BTF','MPF','PNF','MCF')
GROUP BY g.SelCode, g.DailyGoal

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