我有兩張桌子
表 1 - “B2B - 配額（直接）” Q - 每天單行
| Date (dd/mm/yyyy) | Georgina Target| Date Lookup (unix timestamp) |
| :---------------- | :--------------| :-------------------------------|
| 1/1/22 | $10 | unix_timestamp(date) |
| 2/1/22 | $10 | unix_timestamp(date) |
| 3/1/22 | $10 | unix_timestamp(date) |
表 2 - “B2B - 帳戶到發票” A - 每天多行
| Date (dd/mm/yyyy) | Total Directs | Date Lookup (unix timestamp) |
| :---------------- | :--------------| :-------------------------------|
| 1/1/22 | $5 | unix_timestamp(date) |
| 1/1/22 | $9 | unix_timestamp(date) |
| 1/1/22 | $8 | unix_timestamp(date) |
| 3/1/22 | $2 | unix_timestamp(date) |
| 3/1/22 | $3 | unix_timestamp(date) |
| 3/1/22 | $7 | unix_timestamp(date) |
我要回傳的是：
| Date (dd/mm/yyyy) | Total Directs| Georgina Target |
| :---------------- | :----------- | :--------------- |
| 1/1/22 | $22 | $10 |
| 2/1/22 | $0 | $10 |
| 3/1/22 | $12 | $10 |
我目前正在使用這個 SQL：
SELECT
Q."Date" AS "Date",
sum_if(A."Account Type" REGEXP 'direct', A."Invoice Amount") AS "Total Directs",
sum(Q."Georgina Target") AS "Georgina Target"
FROM "B2B - Quotas (DIRECT)" Q
LEFT OUTER JOIN "B2B - Accounts to Invoices" A ON A."Date Lookup" = Q."Date Lookup"
GROUP BY "Date"
問題是“喬治娜目標”是多次求和（基于表中的行數 - “B2B - 帳戶到發票”A 為該日期）。
注意：“Total Directs”可能沒有每個日期的一行，但我需要顯示每個日期（基于表格“B2B - 配額（DIRECT）”）
uj5u.com熱心網友回復：
SUM()的子查詢total_directs。LEFT JOIN和COALESCE()你的注意。假設該QUOTAS表不能在單個日期有多個目標。
SELECT
q.date
,q.target --No need to aggregate since Target has a single value per day
,COALESCE(a.total_directs,0) -- DBMS dependent
FROM b2b_quotas AS q
LEFT JOIN (
SELECT
SUM(total_directs) as total_directs
,date
FROM invoices
GROUP BY date
) AS a ON q.date = a.date
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/460379.html