Mysql带条件计数

Question

我有一个这样的表:

+---------------+--------+-----------------+
| ReservationID | UserID | ReservationDate |
+---------------+--------+-----------------+
|             1 |  10002 | 04_04_2016      |
|             2 |  10003 | 04_04_2016      |
|             3 |  10003 | 07_04_2016      |
|             4 |  10002 | 04_04_2016      |
|             5 |  10002 | 04_04_2016      |
|             6 |  10002 | 06_04_2016      |
+---------------+--------+-----------------+

我使用此查询来计算每个用户有多少预订:

SELECT UserID, COUNT(UserID) as Times FROM mytable GROUP BY UserID ORDER BY Times DESC

它给了我这个结果:

+--------+-------+
| UserID | Times |
+--------+-------+
|  10002 |     4 |
|  10003 |     2 |
+--------+-------+

但是，如果我的用户在特定日期有多个预订，我想算一。例如，用户 10002 在 04-04-2016 有 3 个预订，应该是计数 1。我想得到这个结果:

+--------+-------+
| UserID | Times |
+--------+-------+
|  10002 |     2 |
|  10003 |     2 |
+--------+-------+

我怎样才能得到它？

谢谢

Answer 1

使用 count 和 distinct 代替:

SELECT UserID, COUNT(DISTINCT ReservationDate) as Times 
FROM mytable 
GROUP BY UserID 
ORDER BY Times DESC