mysql - 需要对列中的某些日期值进行计数

Question

我的查询应该计算日期列中的日期:

1. future 日期的出现次数“no”。即将推出'
2. 过去 90 天内出现的日期“编号”目前'
3. 出现超过 90 天的日期“没有”。年纪大了'

我的查询如下所示:

select u.user_id, count(m.theater_release_date < CURDATE()) as 'no. upcoming',
count(m.theater_release_date BETWEEN CURDATE() - INTERVAL 90 DAY AND CURDATE()) as 'no.
currently', count(m.theater_release_date < CURDATE() - INTERVAL 90 DAY) as 'no. older'
from users u join movie m on m.movie_id = u.movie_id
group by u.user_id;

我在日期列中得到了相同的计数，这显然表明我的查询是无用的。

 ---------+--------------+-----------------+------------+
| user_id | no. upcoming | no. currently   | no. older  |
+---------+--------------+-----------------+------------+
|       1 |          197 |             197 |        197 |
|    1929 |           11 |              11 |         11 |
|    2161 |           18 |              18 |         18 |
|    4385 |           10 |              10 |         10 |
|    4897 |            3 |               3 |          3 |
|    5737 |            6 |               6 |          6 |
|    6177 |            4 |               4 |          4 |
|    6289 |           12 |              12 |         12 |

感谢 Fabricator，您的查询返回的结果与我的非常相似，只是现在中间列全 0。

+---------+--------------+---------------+-----------+
| user_id | no. upcoming | no. currently | no. older |
+---------+--------------+---------------+-----------+
|       1 |          197 |             0 |       197 | 
|    1929 |           11 |             0 |        11 |
|    2161 |           18 |             0 |        18 |
|    4385 |           10 |             0 |        10 |
|    4897 |            3 |             0 |         3 |
|    5737 |            6 |             0 |         6 |
|    6177 |            4 |             0 |         4 |
|    6289 |           12 |             0 |        12 |
|    7353 |            6 |             0 |         6 |
|    7417 |            1 |             0 |         1 |
|    7465 |           11 |             0 |        11 |

Answer 1

MySQL 将 bool 值视为整数，true 为 1，false 为 0。因此，您只需将 count() 替换为 sum() 即可:

select u.user_id, sum(m.theater_release_date < CURDATE()) as `no. upcoming`,
       sum(m.theater_release_date BETWEEN CURDATE() - INTERVAL 90 DAY AND CURDATE()) as `no. currently`,
       sum(m.theater_release_date < CURDATE() - INTERVAL 90 DAY) as `no. older`
from users u join
     movie m
     on m.movie_id = u.movie_id
group by u.user_id;

注意:您应该仅对字符串和日期常量使用单引号。使用反引号(在 MySQL 中)转义列别名。