我有以下需要在3个表上运行的简单SQL查询:
SELECT
A.date,
SUM(A.number)
FROM A
GROUP BY
A.date
但是我还有另外两个表(B和C)，我想对它们运行相同的查询.并将结果合并到一个表中作为输出.
我希望输出如下所示:
| date | A.number | B.number | C.number |
|---|---|---|---|
| 2022 | 12322.1 | 9999999 | 888888 |
我有以下需要在3个表上运行的简单SQL查询:
SELECT
A.date,
SUM(A.number)
FROM A
GROUP BY
A.date
但是我还有另外两个表(B和C)，我想对它们运行相同的查询.并将结果合并到一个表中作为输出.
我希望输出如下所示:
| date | A.number | B.number | C.number |
|---|---|---|---|
| 2022 | 12322.1 | 9999999 | 888888 |
我们可以try 以下联合方法:
SELECT
date,
SUM(CASE WHEN src = 'A' THEN number ELSE 0 END) AS A_sum,
SUM(CASE WHEN src = 'B' THEN number ELSE 0 END) AS B_sum,
SUM(CASE WHEN src = 'C' THEN number ELSE 0 END) AS C_sum
FROM
(
SELECT date, number, 'A' AS src FROM A
UNION ALL
SELECT date, number, 'B' FROM B
UNION ALL
SELECT date, number, 'C' FROM C
) t
GROUP BY date
ORDER BY date;