使用 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_scoreFROM (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' UNIONSELECT 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_score2020-01-02    173             62020-05-02    99              21

Answer 2

你有几个小问题：z.dates第一行后面缺少逗号您没有COALESCE函数的默认值 when allscoreisNULL您在子查询 ( 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_scoreFROM (  SELECT dateby AS dates FROM cricket_table  UNION  SELECT dateby FROM football_table) AS zLEFT 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_score2020-01-02  173             62020-05-02  99              21