mysql - 根据日期表计算多列 - MySQL

Question

使用日期表，我试图根据周计算不同的列。我设法用一列来完成，而且效果很好。但是，当我对多列进行计数时，我会得到错误或重复的结果。我认为这是因为加入。

这按预期适用于一列:

 SELECT
 DATE_FORMAT(thedate, '%u') as week
,COUNT(t.completed_date) as completed

FROM datetable
LEFT JOIN projects t ON t.completed_date = thedate
WHERE thedate BETWEEN YEAR(NOW()) AND NOW()
GROUP BY YEARWEEK(thedate,7)

通过将 ,COUNT(t.sales_date) as sales 添加到选择中，我将获得已完成和销售额的重复计数。

基于此样本(项目)

| id | completed_date | sales_date |
| 1  | NULL           | NULL       |
| 2  | NULL           | 2013-08-26 |
| 3  | NULL           | 2013-08-28 |
| 4  | 2013-09-06     | NULL       |

我在找

| week | completed | sales |
| 34   | 0         | 0     |
| 35   | 0         | 2     |
| 36   | 1         | 0     |

我正在使用日期表，因为当没有日期时我需要所有日期都为 0。我想我可以通过子查询来解决它，但是我还需要在此查询中计算其他 12 个日期字段(不包括在示例中)。

有没有比使用大量子查询更好的方法来解决这个问题？我的 SQL 有点生疏。

Answer 1

一种方法是使用子查询按周对每个值进行分组，然后将它们连接在一起。

SELECT d.week, completed, sales
FROM (SELECT YEARWEEK(thedate) week
      FROM datetable
      WHERE thedate BETWEEN YEAR(NOW()) AND NOW()
      GROUP BY week) d
LEFT JOIN (SELECT YEARWEEK(completed_date) week, COUNT(*) completed
           FROM projects
           WHERE completed_date BETWEEN YEAR(NOW()) AND NOW()
           GROUP BY week) c
ON c.week = d.week
LEFT JOIN (SELECT YEARWEEK(sales_date) week, COUNT(*) sales
           FROM projects
           WHERE sales_date BETWEEN YEAR(NOW()) AND NOW()
           GROUP BY week) s
ON s.week = d.week

这种方式更容易扩展到额外的列:

SELECT DATE_FORMAT(thedate, '%u') AS week,
       IFNULL(SUM(completed_date = thedate), 0) AS completed,
       IFNULL(SUM(sales_date = thedate), 0) AS sales
FROM datetable
LEFT JOIN projects
ON thedate IN (completed_date, sales_date)
WHERE thedate BETWEEN YEAR(NOW()) AND NOW()
GROUP BY week