我想按每個日期從多個表組中獲取兩個日期之間每個表的總和。我的代碼只獲取一個分數，而不是每個日期的所有分數的總和。SELECT z.dates COALESCE(a.allscore) AS cricket_score, COALESCE(b.allscore) AS football_score FROM ( SELECT dateby AS dates FROM cricket_table UNION SELECT dateby FROM football_table) AS z LEFT JOIN (SELECT dateby AS dates, SUM(score) AS allscore FROM cricket_table WHERE dateby BETWEEN '2020-01-01' AND '2020-03-03' GROUP BY dates) a USING (dates) LEFT JOIN (SELECT dateby AS dates, SUM(score) AS allscore FROM football_table WHERE dateby BETWEEN '2020-01-01' AND '2020-03-03' GROUP BY dates) b USING (dates)GROUP BY z.dates【完整代碼與數據庫結構】$sql = "create table cricket_table (player varchar(50), score int, dateby date);create table football_table (player varchar(50), score int, dateby date);insert into cricket_table values('Sohail',32, '2020-01-02'),('Saleem',65, '2020-01-02'), ('Hamid',76, '2020-01-02'),('Sohail',33, '2020-05-02'),('Saleem',44, '2020-05-02'),('Hamid',22, '2020-05-02');insert into football_table values ('Sohail',1, '2020-01-02'),('Saleem',2, '2020-01-02'),('Hamid',3, '2020-01-02'),('Sohail',6, '2020-05-02'),('Saleem',7, '2020-05-02'),('Hamid',8, '2020-05-02');";$result = $db->prepare($sql);$result->execute();$query = $db->prepare("SELECT z.dates COALESCE(a.allscore) AS cricket_score, COALESCE(b.allscore) AS football_score FROM ( SELECT dateby AS dates FROM cricket_table UNION SELECT dateby FROM football_table) AS z LEFT JOIN (SELECT dateby AS dates, SUM(score) AS allscore FROM cricket_table WHERE dateby BETWEEN '2020-01-01' AND '2020-03-03' GROUP BY dates) a USING (dates) LEFT JOIN (SELECT dateby AS dates, SUM(score) AS allscore FROM football_table WHERE dateby BETWEEN '2020-01-01' AND '2020-03-03' GROUP BY dates) b USING (dates) GROUP BY dates");（https://pastebin.com/prvsd1X8）
2 回答
烙印99
TA貢獻1829條經驗 獲得超13個贊
因此，如果您想要按日期計算的 cricket_score 和 football_score，請檢查以下查詢是否符合您的期望
SELECT dateby, SUM(cricket_score) AS cricket_score, SUM(football_score) AS football_score
FROM (
SELECT c.dateby, c.score AS cricket_score, 0 AS football_score
FROM cricket_table AS c
WHERE c.dateby BETWEEN '2020-01-01' AND '2020-07-05'
UNION
SELECT f.dateby, 0 AS cricket_score, f.score AS football_score
FROM football_table AS f
WHERE f.dateby BETWEEN '2020-01-01' AND '2020-07-05'
) AS temp GROUP BY dateby
一種簡單易行的方法
演示：http ://sqlfiddle.com/#!18/fc606/15
輸出
dateby cricket_score football_score
2020-01-02 173 6
2020-05-02 99 21
ABOUTYOU
TA貢獻1812條經驗 獲得超5個贊
你有幾個小問題：
z.dates第一行后面缺少逗號您沒有
COALESCE函數的默認值 whenallscoreisNULL您在子查詢 (
BETWEEN '2020-01-01' AND '2020-03-03') 中的日期范圍不包括cricket_table和football_table您不需要
GROUP BY外部查詢。
所以你的查詢應該是這樣的：
SELECT z.dates,
COALESCE(a.allscore, 0) AS cricket_score,
COALESCE(b.allscore, 0) AS football_score
FROM (
SELECT dateby AS dates FROM cricket_table
UNION
SELECT dateby FROM football_table
) AS z
LEFT JOIN (SELECT dateby AS dates, SUM(score) AS allscore
FROM cricket_table
WHERE dateby BETWEEN '2020-01-01' AND '2020-05-03'
GROUP BY dates
) a USING (dates)
LEFT JOIN (SELECT dateby AS dates, SUM(score) AS allscore
FROM football_table
WHERE dateby BETWEEN '2020-01-01' AND '2020-05-03'
GROUP BY dates
) b USING (dates)
樣本數據的輸出：
dates cricket_score football_score
2020-01-02 173 6
2020-05-02 99 21
- 2 回答
- 0 關注
- 191 瀏覽
添加回答
舉報
0/150
提交
取消