我正在嘗試在給定的時間范圍內創建報告并計算檔案/專案。我認為我在 where 子句上做錯了。
我有的：
- 帶有專案 ID 的專案表
- 不同的檔案表，每個表都有一個檔案 ID、專案 ID 和檔案創建日期：
專案表
| ID |
|---|
| 1 |
| 2 |
| 3 |
| 4 |
檔案型別 A 表
| ID | 專案編號 | 創建時間 |
|---|---|---|
| 1 | 2 | 2022-02-12T05:24:22.420 |
| 2 | 3 | 2022-04-12T05:24:22.420 |
| 3 | 3 | 2022-03-12T05:24:22.420 |
| 4 | 3 | 2022-02-12T05:24:22.420 |
檔案型別 B 表
| ID | 專案編號 | 創建時間 |
|---|---|---|
| 1 | 4 | 2022-01-12T05:24:22.420 |
| 2 | 3 | 2022-01-12T05:24:22.420 |
| 3 | 3 | 2022-05-12T05:24:22.420 |
預期結果：
給定時間范圍從 2022-01 到 2022-03
| 專案編號 | 計算檔案 a | 計數檔案 b |
|---|---|---|
| 1 | 0 | 0 |
| 2 | 1 | 0 |
| 3 | 2 | 1 |
| 4 | 0 | 1 |
我嘗試了以下選擇：
SELECT "project"."id",
COUNT(DISTINCT(documenta.id)) AS documentA_total
COUNT(DISTINCT(documentb.id)) AS documentB_total,
FROM project
LEFT JOIN documenta ON documenta.projectid = project.id
LEFT JOIN documentb ON documentb.projectid = project.id
WHERE
("documenta"."createdAt" BETWEEN '{{daterange.start}}' AND '{{daterange.end}}')
OR
("documentb"."createdAt" BETWEEN '{{daterange.start}}' AND '{{daterange.end}}')
GROUP BY project.id;
我認為問題出在 where 子句上。因為我得到了錯誤的計數。
uj5u.com熱心網友回復：
您需要更新連接條件以包含日期過濾器并將它們從 where 子句中洗掉。為簡單起見，我重命名了表名和列名。 這是sqlfiddle
SELECT p.id,
COUNT(DISTINCT(a.id)) AS documentA_total,
COUNT(DISTINCT(b.id)) AS documentB_total
FROM project p
LEFT JOIN docA a ON p.id = a.project_id and a.created_at BETWEEN '2022-01-01' AND '2022-03-31'
LEFT JOIN docB b ON p.id = b.project_id and b.created_at BETWEEN '2022-01-01' AND '2022-03-31'
GROUP BY p.id;
為簡單起見，我重命名了表名和列名。您的確切查詢將類似于以下查詢，
SELECT "project"."id",
COUNT(DISTINCT(documenta.id)) AS documentA_total
COUNT(DISTINCT(documentb.id)) AS documentB_total,
FROM project
LEFT JOIN documenta ON documenta.projectid = project.id and "documenta"."createdAt" BETWEEN '{{daterange.start}}' AND '{{daterange.end}}'
LEFT JOIN documentb ON documentb.projectid = project.id and "documentb"."createdAt" BETWEEN '{{daterange.start}}' AND '{{daterange.end}}'
GROUP BY project.id;
uj5u.com熱心網友回復：
您的代碼不是 mysql 代碼，因為它不允許使用雙引號的列名
但是，您仍然必須在加入之前計算資料
SELECT "project"."id",
COALESCE(documentA_total,0) AS documentA_total,
COALESCE(documentB_total,0) AS documentB_total
FROM project
LEFT JOIN (SELECT Count(*) as documentA_total, projectid FROM documenta WHERE
("documenta"."createdAt" BETWEEN '{{daterange.start}}' AND '{{daterange.end}}') GROUP BY projectid) as doca ON doca.projectid = project.id
LEFT JOIN (SELECT Count(*) as documentB_total, projectid FROM documentb WHERE ("documentb"."createdAt" BETWEEN '{{daterange.start}}' AND '{{daterange.end}}')
GROUP BY projectid) as docb ON docb.projectid = project.id
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/491395.html