mysql - 无论如何如何将消息为零的日期包含到结果集中？

Question

我有下表的消息:

+---------+---------+------------+----------+
| msg_id  | user_id | m_date     |  m_time  |
+-------------------+------------+----------+
|   1     | 1       | 2011-01-22 | 06:23:11 |
|   2     | 1       | 2011-01-23 | 16:17:03 |
|   3     | 1       | 2011-01-23 | 17:05:45 |
|   4     | 2       | 2011-01-22 | 23:58:13 |
|   5     | 2       | 2011-01-23 | 23:59:32 |
|   6     | 2       | 2011-01-24 | 21:02:41 |
|   7     | 3       | 2011-01-22 | 13:45:00 |
|   8     | 3       | 2011-01-23 | 13:22:34 |
|   9     | 3       | 2011-01-23 | 18:22:34 |
|  10     | 3       | 2011-01-24 | 02:22:22 |
|  11     | 3       | 2011-01-24 | 13:12:00 |
+---------+---------+------------+----------+

我想要的是每天，查看每个用户在 16:00 之前和之后发送了多少条消息:

SELECT 
    user_id, 
    m_date, 
    SUM(m_time <= '16:00') AS before16, 
    SUM(m_time > '16:00') AS after16 
FROM messages 
GROUP BY user_id, m_date
ORDER BY user_id, m_date ASC

这会产生:

user_id m_date      before16  after16
-------------------------------------
1       2011-01-22  1         0
1       2011-01-23  0         2
2       2011-01-22  0         1
2       2011-01-23  0         1
2       2011-01-24  0         1
3       2011-01-22  1         0
3       2011-01-23  1         1
3       2011-01-24  2         0

因为用户 1 在 2011-01-24 没有写任何消息，所以这个日期不在结果集中。然而，这是不希望的。我的数据库中有第二个表，称为“date_range”:

+---------+------------+
| date_id | d_date     |
+---------+------------+
| 1       | 2011-01-21 |
| 1       | 2011-01-22 |
| 1       | 2011-01-23 |
| 1       | 2011-01-24 |
+---------+------------+

我想检查此表的“消息”。对于每个用户，所有这些日期都必须在结果集中。如您所见，没有用户在 2011-01-21 写过消息，并且如上所述，用户 1 在 2011-01-24 没有消息。查询的期望输出将是:

user_id d_date      before16  after16
-------------------------------------
1       2011-01-21  0         0
1       2011-01-22  1         0
1       2011-01-23  0         2
1       2011-01-24  0         0
2       2011-01-21  0         0
2       2011-01-22  0         1
2       2011-01-23  0         1
2       2011-01-24  0         1
3       2011-01-21  0         0
3       2011-01-22  1         0
3       2011-01-23  1         1
3       2011-01-24  2         0

我如何链接这两个表，以便查询结果也包含 before16 和 after16 的值为零的行？

编辑:是的，我有一个“用户”表:

+---------+------------+
| user_id | user_date  |
+---------+------------+
| 1       | foo        |
| 2       | bar        |
| 3       | foobar     |
+---------+------------+

Answer 1

测试台:

create table messages (msg_id integer, user_id integer, _date date, _time time);
create table date_range (date_id integer, _date date);
insert into messages values
       (1,1,'2011-01-22','06:23:11'),
       (2,1,'2011-01-23','16:17:03'),
       (3,1,'2011-01-23','17:05:05');
insert into date_range values
       (1, '2011-01-21'),
       (1, '2011-01-22'),
       (1, '2011-01-23'),
       (1, '2011-01-24');

查询:

SELECT p._date, p.user_id,
       coalesce(m.before16, 0) b16, coalesce(m.after16, 0) a16
  FROM
      (SELECT DISTINCT user_id, dr._date FROM messages m, date_range dr) p
  LEFT JOIN
      (SELECT user_id, _date,
              SUM(_time <= '16:00') AS before16,
              SUM(_time > '16:00') AS after16 
         FROM messages 
        GROUP BY user_id, _date
        ORDER BY user_id, _date ASC) m
    ON p.user_id = m.user_id AND p._date = m._date;

编辑:

1. 您的初始查询保留原样，我希望它不需要任何解释；

2. SELECT DISTINCT user_id, dr._date FROM messages m, date_range dr 将返回笛卡尔坐标或 CROSS JOIN两个表，这将为我提供主题中每个用户的所有必需日期范围。由于我只对每对感兴趣一次，因此我使用了 DISTINCT 子句。尝试使用和不使用此查询；

3. 然后我使用 LEFT JOIN在两个子选择上。

   这个join的意思是:首先进行INNER join，即返回所有在ON条件下匹配字段的行。然后，对于在右侧没有匹配项的联接的左侧关系中的每一行，返回 NULL(因此名称为 LEFT JOIN，即左关系总是存在的，并且 right 应该有 NULL)。此连接将按照您的预期进行 — 返回 user_id + date 组合，即使给定用户在给定日期没有任何消息也是如此。请注意，我首先使用 user_id + date 子选择(在左侧)，然后使用 messages 查询(在右侧)；

4. coalesce()用于将 NULL 替换为零。

我希望这能阐明此查询的工作原理。