PostgreSQL查询求和问题：多路由ID致多行结果需合并为单行

问题分析 #

你的查询返回多行是因为GROUP BY子句中包含了routes.id，每个不同的路由ID会单独生成一行统计结果。要合并为单行并对totalCheck（即routes.totalSpots）求和，需要调整分组逻辑和聚合方式。

修改方案 #

1. 移除GROUP BY中的routes.id，不再按路由分组
2. 将routes."totalSpots" AS "totalCheck"改为SUM(routes."totalSpots") AS "totalCheck"，实现对所有符合条件路由的totalSpots求和
3. 其余聚合函数会自动基于所有符合条件的记录进行全局统计

修改后的完整查询语句 #

select "users"."id",
        count((cycles.adherence >= 100 and cycles.justified = false) or null) as "routesDone",
        count((cycles.adherence < 100 and cycles.opened = false) or null) as "routesNotDone", 
        count((cycles.opened = true and cycles.adherence < 100) or null) as "routesOpened", 
        count(cycles.justified or null) as "routesJustified",
        SUM(routes."totalSpots") AS "totalCheck",
        count(cycles.id) as "routesOnPeriod",
        (
             count((cycles.adherence >= 100 and cycles.justified = false) or null) +
             count((cycles.adherence < 100 and cycles.opened = false) or null) +
             count((cycles.opened = true and cycles.adherence < 100) or null) +
             count(cycles.justified or null)
        ) as "routesTotal", 
        0 as "routesDoneLate", 
        0 as "routesLate", 
        SUM(case when cycles.adherence >= 100 then routes."totalAssets" else 0 end) AS "coveredAssets", 
        SUM(case when cycles.adherence < 100 then routes."totalAssets" else 0 end) AS "uncoveredAssets" 
from "routes" 
    inner join "cycles" on "routes"."identifier" = "cycles"."routeIdentifier" 
    inner join "workspaces" on "cycles"."workspaceId" = "workspaces"."id" 
    inner join "users" on "users"."id" = "cycles"."inspectorId" 
where "routes"."deleted_at" is null 
    and "cycles"."deletedAt" is null 
    and "workspaces"."deleted" = false
    and "cycleStartAt" <= '2022-07-11T02:59:59Z' 
    and "cycleEndAt" >= '2022-04-12T03:00:00Z'
    and "users"."id" = 'b67830a7-39fc-4ad5-bf26-07a43dcd3676'
    and "routes"."contextPath" like '/malicious-interaction-murder-32%'
group by 
    users.id

补充说明 #

因为WHERE子句已经指定了单个用户ID（users.id = 'b67830a7-39fc-4ad5-bf26-07a43dcd3676'），GROUP BY users.id实际上只会生成一行结果，所有聚合统计都会基于该用户的全部符合条件的记录计算。

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