使用 UNION 获取日期范围之间的 SUM

Question

我想按每个日期从多个表组中获取两个日期之间每个表的总和。我的代码只获取一个分数，而不是每个日期的所有分数的总和。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）

Answer 1

因此，如果您想要按日期计算的 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

Answer 2

你有几个小问题：

1. z.dates第一行后面缺少逗号

2. 您没有COALESCE函数的默认值 when allscoreisNULL

3. 您在子查询 ( BETWEEN '2020-01-01' AND '2020-03-03') 中的日期范围不包括cricket_table和football_table

4. 您不需要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