mysql 在某个时间之后_mysql – 在某个时间之前和之后计算每天的消息并在一个结果集中显示...

我有下面的消息表：

+---------+---------+------------+----------+

| msg_id | user_id | _date | _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 | 3 | 2011-01-22 | 13:45:00 |

| 7 | 3 | 2011-01-23 | 13:22:34 |

| 8 | 3 | 2011-01-23 | 18:22:34 |

+---------+---------+------------+----------+

我想要的是每天,看看每个用户在16:00之前和之后发送了多少条消息.我现在分两步完成：

SELECT user_id, _date, COUNT(msg_id) AS cnt

FROM messages WHERE _time <= '16:00'

GROUP BY user_id, _date ORDER BY user_id, _date ASC

user_id _date cnt

-----------------------------

1 2011-01-22 1

1 2011-01-23 0

2 2011-01-22 0

2 2011-01-23 0

3 2011-01-22 1

3 2011-01-23 1

SELECT user_id, _date, COUNT(msg_id) AS cnt

FROM messages WHERE _time > '16:00'

GROUP BY user_id, _date ORDER BY user_id, _date ASC

user_id _date cnt

-----------------------------

1 2011-01-22 0

1 2011-01-23 2

2 2011-01-22 1

2 2011-01-23 1

3 2011-01-22 0

3 2011-01-23 1

(实际上,顺便说一下,结果集中没有给出“0”值的行.我只是添加了它们以便澄清)

我想将这两个输出合并为一个：

user_id _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

3 2011-01-22 1 0

3 2011-01-23 1 1

但是,我不知道如何编写此查询.如果你这样做,你的帮助将不胜感激:-)