我有兩張桌子，
表A
SELECT SUM(amount) AS amount, DATE_FORMAT(date,'%m %y') AS date FROM `TableA` GROUP BY DATE_FORMAT(date,'%m%y');
| amount | date |
| -------- | -------- |
| 11 | 05 22 |
| 22 | 06 22 |
| 33 | 07 22 |
| 44 | 08 22 |
| 55 | 09 22 |
| 66 | 10 22 |
表B
SELECT SUM(amount) AS amount, DATE_FORMAT(date,'%m %y') AS date FROM `TableB` GROUP BY DATE_FORMAT(date,'%m%y');
| amount | date |
| -------- | -------- |
| 77 | 07 22 |
| 88 | 08 22 |
| 99 | 09 22 |
| 111 | 10 22 |
| 222 | 11 22 |
最終輸出按日期如下所示，如果其中一個表沒有日期，則金額將為 0。
| amount(Table A)| amount(Table B)| date |
| -------------- | -------------- | ----- |
| 11 | 0 | 05 22 | ←
| 22 | 0 | 06 22 | ←
| 33 | 77 | 07 22 |
| 44 | 88 | 08 22 |
| 55 | 99 | 09 22 |
| 66 | 111 | 10 22 |
| 0 | 222 | 11 22 | ←
表A和表B是按日期分組求和的結果。
uj5u.com熱心網友回復：
如果不存在，則使用 full outer join on 和 coalesce 函式默認amount為 0。
-- 1. Create tables
create table table_a
(
mo_yr varchar(5),
amount double
);
create table table_b
(
mo_yr varchar(5),
amount double
);
-- 2. Insert data
insert into table_a
values
('05 22', 11),
('06 22', 22),
('07 22', 33),
('08 22', 44),
('09 22', 55),
('10 22', 66);
insert into table_b
values
('07 22', 22),
('08 22', 22),
('09 22', 22),
('10 22', 22),
('11 22', 22);
-- 3. SQL query
select COALESCE(a.amount ,0) as amount_a,
COALESCE(b.amount, 0) as amount_b,
COALESCE(a.mo_yr, b.mo_yr) as yr_mo
from table_a a
full
join table_b b
using (mo_yr)
order by mo_yr;
結果：
amount_a|amount_b|yr_mo|
-------- -------- -----
11.0| 0.0|05 22|
22.0| 0.0|06 22|
33.0| 22.0|07 22|
44.0| 22.0|08 22|
55.0| 22.0|09 22|
66.0| 22.0|10 22|
0.0| 22.0|11 22|
編輯：另一個是 UNION ALL，然后是 GROUP BY，如下所示：
with cte as (
select mo_yr, amount as amount_a, 0 as amount_b from table_a
union all
select mo_yr, 0 as amount_a, amount as amount_b from table_b)
select sum(amount_a) as amount_a,
sum(amount_b) as amount_b,
mo_yr
from cte
group by mo_yr
order by mo_yr;
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/535619.html
標籤：数据库数据库