我很難找到有效的解決方案來解決我的問題。我認為這是一個相當普遍的問題，這就是我在這里提出來的原因。
這是問題所在。
假設我有多個選擇，例如以下選擇：
選擇創建
| 日期 | num_created |
|---|---|
| 01-01-2021 | 10 |
| 01-02-2021 | 2 |
| 01-04-2021 | 13 |
SELECT Update
| 日期 | num_update |
|---|---|
| 01-01-2021 | 14 |
| 01-02-2021 | 2 |
| 01-03-2021 | 9 |
SELECT Delete
| 日期 | num_delete |
|---|---|
| 01-02-2021 | 2 |
| 01-05-2021 | 40 |
我想要這個最終輸出
Final output
| 日期 | num_created | num_update | num_deleted |
|---|---|---|---|
| 01-01-2021 | 10 | 14 | 0 |
| 01-02-2021 | 2 | 2 | 2 |
| 01-03-2021 | 0 | 9 | 0 |
| 01-04-2021 | 13 | 0 | 0 |
| 01-05-2021 | 0 | 0 | 40 |
*我不能假設任何表格都包含所有日期或匹配日期
uj5u.com熱心網友回復：
看起來您想要按天分組的計數：
select
tbls.date,
sum(tbls.num_created) as num_created,
sum(tbls.num_updated) as num_updated,
sum(tbls.num_deleted) as num_deleted
from (
select date, num_created, 0 as num_updated, 0 as num_deleted from tbl_create
union all
select date, 0 as num_created, num_updated, 0 as num_deleted from tbl_update
union all
select date, 0 as num_created, 0 as num_updated, num_deleted from tbl_delete) as tbls
group by tbls.date
請不要在不需要時濫用“with statement”（如其他答案中所示）。
uj5u.com熱心網友回復：
您可以在表和 case 陳述句之間執行完全外連接以選擇如下日期（假設 3 個表/查詢為 a、b、c）：
With temp as
(
select case when a.date is not null then a.date else b.date end as date,num_created,num_update from a full join b on (a.date=b.date)
),
temp1 as
(
select case when temp.date is not null then temp.date else c.date end as date,num_created,num_update,num_delete from temp full join c on (temp.date=c.date)
)
select * from temp1;
uj5u.com熱心網友回復：
假設表是：
tbl_create(dt, num_created)
tbl_update(dt, num_updated)
tbl_delete(dt, num_deleted)
從包含 3 個源表中的所有日期的（臨時）表開始：all_dates
然后（左外）加入來自 3 個源表的計數器。
注意：'union' 洗掉重復項，無需擔心（與 'union all' 相反）
注意：coalesce(a,b) 回傳第一個非空引數，所以你有零而不是 'null's
with all_dates(dt) as (
select dt from tbl_create
union
select dt from tbl_update
union
select dt from tbl_delete
)
select all_dates.dt
, coalesce(tbl_create.num_created,0) --
, coalesce(tbl_update.num_updated,0)
, coalesce(tbl_delete.num_deleted,0)
from all_dates
left outer join tbl_create on all_dates.dt = tbl_create.dt
left outer join tbl_update on all_dates.dt = tbl_update.dt
left outer join tbl_delete on all_dates.dt = tbl_delete.dt
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/367510.html
上一篇：獲取唯一的事件計數
下一篇：根據其他3列的值創建一列