sql - 添加两个 COUNT 个结果

Question

我在添加两个选择结果时遇到了问题。我的代码是:

SELECT a AS total_rows, b
FROM (
   SELECT COUNT (*) as a, E.b as b
   FROM         table1 C
   JOIN         table2 D
   ON           D.MY_ID = C.MY_ID
   JOIN         table3 E
   ON           E.NEW_ID = C.NEW_ID
   WHERE        E.b BETWEEN 1 AND 4
   GROUP BY     E.b
   ORDER BY     E.b
   ) 
UNION ALL (
       SELECT COUNT(*)as a, E.b as b
       FROM          table2 D
       JOIN          table4 S
       ON            S.PERSON_ID IN (D.f_ID, D.g_ID)
       JOIN          table3 E
       ON            E.LOCATION_ID IN (Q.LOC_FROM_ID, Q.LOC_TO_ID)
       WHERE         L.AISLE BETWEEN 1 AND 8
       GROUP BY      E.b
       ORDER BY      E.b)

当我单独使用它们时，每个 Select 都能完美工作。当我在不选择 E.b. 的情况下尝试这样做时，它也有效。

我要实现的目标是显示相互添加的两个选择的结果，但它必须按 E.b 列分组，并且 E.b 列必须在结果中可见。

Answer 1

将您的UNION ALL 查询用作子查询 并获取计数的SUM。

WITH data AS(
   SELECT COUNT (*) as a, E.b as b
   FROM         table1 C
   JOIN         table2 D
   ON           D.MY_ID = C.MY_ID
   JOIN         table3 E
   ON           E.NEW_ID = C.NEW_ID
   WHERE        E.b BETWEEN 1 AND 4
   GROUP BY     E.b
   ORDER BY     E.b
UNION ALL 
       SELECT COUNT(* )as a, E.b as b
       FROM          table2 D
       JOIN          table4 S
       ON            S.PERSON_ID IN (D.f_ID, D.g_ID)
       JOIN          table3 E
       ON            E.LOCATION_ID IN (Q.LOC_FROM_ID, Q.LOC_TO_ID)
       WHERE         L.AISLE BETWEEN 1 AND 8
       GROUP BY      E.b
       ORDER BY      E.b
)
SELECT sum(A) total, b 
FROM data
GROUP BY b;