如何根据列值和按日期分组返回行？

Question

我有一个这样结构的表：

电影：

id | date_entered_theater | date_left_theater

我想要编写一个查询，返回每天影院中的电影数量。当date_entered_theater列不为null且date_left_theater列为null时，电影在影院中。

日期列使用datetime格式，查询应返回如下数据：

date         moviesInTheater
2014-09-22 | 5
2014-09-23 | 6
2014-09-24 | 8
2014-09-25 | 7

我不确定如何编写这个查询。我尝试了以下几种方法：

SELECT DATE(date_entered_theater), COUNT(*) as moviesInTheater
FROM movies
GROUP BY DATE(date_entered_theater)
HAVING date_entered_theater IS NOT NULL AND date_left_theater IS NULL
ORDER BY date_entered_theater ASC

它会导致错误[Err] 1054 - Unknown column 'date_left_theater' in 'having clause'

我也不认为上面的查询是正确的，因为它按date_entered_theater分组，没有考虑date_left_theater，这意味着一部电影可能会在没有电影进入影院的一天离开影院，并且这种更改不会反映在结果集中。

我所要求的可以在MySQL中完成吗？

Answer 1

条件检查应该是WHERE格式，而不是HAVING格式。

HAVING子句中只应包含基于聚合函数的条件。

SELECT DATE(date_entered_theater), COUNT(*) as moviesInTheater
FROM movies
WHERE date_entered_theater IS NOT NULL AND date_left_theater IS NULL
GROUP BY DATE(date_entered_theater)
ORDER BY date_entered_theater ASC

EDIT:如果你想知道某一天放映的电影的数量，你可能需要下面这样的东西。当我有时间的时候，我会试着发布一个小提琴。

SET @num = -1;
SELECT
    date_sequence,
    COUNT(m.id) as moviesInTheater
FROM movies as m
    JOIN (SELECT DATE_ADD( '2014-09-01', 
                  interval @num := @num+1 day) AS date_sequence
          HAVING DATE_ADD('2014-09-01', interval @num day) <= '2014-09-25') as dt
         ON m.date_entered_theater >=dt. date_sequence
GROUP BY dt.date_sequence

Answer 2

首先，您需要一个表，在一列中包含您关心的时间跨度的所有日期(例如一年)。您不能使用其他表，因为列movies.date_entered_theater中没有包含所有日期的保修期。

您可以使用您最喜欢的编程语言使用循环填充它。

假设这个表存在，名为movies_daily，并且有一列day作为主键。

如果当前日期介于电影进入影院的日期和离开影院的日期之间，则该电影在影院中。

如果电影离开影院的日期为空，则该电影仍在影院中：

SELECT
  movies_daily.day
  , COUNT(*) AS moviesInTheater
FROM
  movies_daily
  LEFT JOIN movies
    ON movies_daily.day >= movies.date_entered_theater
    AND (movies_daily.day < movies.date_left_theater 
         OR movies.date_left_theater IS NULL)
GROUP BY
  movies_daily.day
ORDER BY
  movies_daily.day