如何在SQL Server中将多行合并为单行？含UNION查询场景

嘿，我看你现在的查询是通过多个UNION分支把不同表的统计结果拆成了多行——同一个ThanaID和RoadTypeId会出现好几次，每次只有一列有实际数值，其他都是0对吧？要把这些行合并成一行，其实不用绕远路，直接用聚合函数把同组的统计值累加就行，下面给你具体的实现方法：

最高效的实现方式：直接聚合多个数据源 #

你的每个UNION分支都是针对ThanaID和RoadTypeId这两个维度做单一统计，完全可以把这些统计逻辑整合到一个查询里，避免生成多行再合并。比如：

假设第三个UNION分支是统计道路长度相关的字段，我们可以用LEFT JOIN把多个表连起来，然后分别统计：

SELECT
    -- 用COALESCE确保分组ID不为空，适配某个表没有对应数据的情况
    COALESCE(R.ThanaID, T.ThanaID, B.ThanaID) AS ThanaID,
    COALESCE(R.RoadTypeId, T.RoadTypeId, B.RoadTypeId) AS RoadTypeId,
    COUNT(R.Id) AS NoRdExist,          -- 来自RoadInventory的道路数量
    COUNT(T.Id) AS NoRdImp,            -- 来自RoadInventory_Temp的道路数量
    SUM(R.BcLen) AS BcLenExist,        -- 来自RoadInventory的道路长度
    SUM(T.BcLen) AS BcLenImp           -- 来自RoadInventory_Temp的道路长度
FROM
    Thana AS A
-- 左连接各个数据源，确保即使某个表没有对应数据也能保留分组
LEFT JOIN RoadInventory AS R ON R.ThanaID = A.Id
LEFT JOIN RoadInventory_Temp AS T ON T.ThanaID = A.Id
-- 如果第三个分支是另一个表，继续左连接这里
LEFT JOIN RoadBcLength AS B ON B.ThanaID = A.Id AND B.RoadTypeId = R.RoadTypeId
GROUP BY
    COALESCE(R.ThanaID, T.ThanaID, B.ThanaID),
    COALESCE(R.RoadTypeId, T.RoadTypeId, B.RoadTypeId)

如果你的统计是来自同一个表的不同条件（比如区分已存在和已实施的道路），那用条件聚合更简单：

SELECT
    R.ThanaID,
    R.RoadTypeId,
    -- 只统计IsExist=1的道路数量
    COUNT(CASE WHEN R.IsExist = 1 THEN R.Id END) AS NoRdExist,
    -- 只统计IsImp=1的道路数量
    COUNT(CASE WHEN R.IsImp = 1 THEN R.Id END) AS NoRdImp,
    -- 累加IsExist=1的道路长度
    SUM(CASE WHEN R.IsExist = 1 THEN R.BcLen END) AS BcLenExist,
    -- 累加IsImp=1的道路长度
    SUM(CASE WHEN R.IsImp = 1 THEN R.BcLen END) AS BcLenImp
FROM
    RoadInventory AS R
INNER JOIN Thana AS A ON R.ThanaID = A.Id
GROUP BY
    R.ThanaID, R.RoadTypeId

如果你想保留原UNION结构怎么办？ #

要是你不想改底层的UNION逻辑，也可以在外层套一个聚合查询，把同组的行合并起来，记得用UNION ALL代替UNION（因为你的分支不会有重复行，UNION ALL性能更高）：

SELECT
    ThanaID,
    RoadTypeId,
    SUM(NoRdExist) AS NoRdExist,
    SUM(NoRdImp) AS NoRdImp,
    SUM(BcLenExist) AS BcLenExist,
    SUM(BcLenImp) AS BcLenImp
FROM (
    -- 你的原UNION查询，替换成UNION ALL
    SELECT R.ThanaID, R.RoadTypeId, Count(R.Id) AS NoRdExist, 0 AS NoRdImp, 0 AS BcLenExist, 0 AS BcLenImp 
    FROM RoadInventory AS R INNER JOIN Thana AS A ON R.ThanaID = A.Id 
    GROUP BY R.ThanaID,R.RoadTypeId 
    UNION ALL
    SELECT R.ThanaID, R.RoadTypeId, 0 AS NoRdExist, Count(R.Id) AS NoRdImp,0 AS BcLenExist, 0 AS BcLenImp 
    FROM RoadInventory_Temp AS R INNER JOIN Thana AS A ON R.ThanaID = A.Id 
    GROUP BY R.ThanaID, R.RoadTypeId 
    UNION ALL
    -- 补全你的第三个UNION分支
    SELECT R.ThanaID, R.RoadTypeId, 0 AS NoRdExist, 0 AS NoRdImp, SUM(R.BcLen) AS BcLenExist, 0 AS BcLenImp 
    FROM RoadBcData AS R INNER JOIN Thana AS A ON R.ThanaID = A.Id 
    GROUP BY R.ThanaID, R.RoadTypeId
) AS UnionResults
GROUP BY ThanaID, RoadTypeId

几点小提示 #

• 优先选单一查询+LEFT JOIN/条件聚合的方案，比UNION再聚合的速度快很多，尤其是数据量大的时候；
• COALESCE()用来处理某个表没有对应分组数据的情况，避免出现NULL的分组ID；
• 用UNION ALL代替UNION能跳过去重步骤，提升查询效率。

内容的提问来源于stack exchange，提问作者Tousif Md. Amin Faisal