mysql - 同时使用 DISTINCT 和 GROUP BY 时只计算第一个实例

Question

假设我有下表，

    timedate    id
    2015-01-01   1
    2015-01-01   2
    2015-01-01   3
    2015-01-01   4
    2015-01-02   1
    2015-01-02   2
    2015-01-02   5
    2015-01-02   6
    2015-01-03   2
    2015-01-03   3

这个查询

 SELECT 
       COUNT(DISTINCT `id`) as total,
        timedate
 FROM Table1
 GROUP BY timedate

产生以下结果

total    timedate
  4      2015-01-01
  4      2015-01-02
  2      2015-01-03

因为它计算的是每个组中不同的 id。

如何在不计算后续组中的情况下获取每个不同 ID 的计数？例如像这样的结果:

total    timedate
  4      2015-01-01
  2      2015-01-02
  0      2015-01-03

Answer 1

最好在这里使用两个子查询。一个获取 id 和它的 min(timedate) 另一个获取不同的时间日期:

SELECT 
       COUNT(DISTINCT `id`) as total,
        t1.timedate
 FROM 
     (SELECT DISTINCT TimeDate FROM Table1) as t1
     LEFT OUTER JOIN (SELECT min(Timedate) as firsttimedate, id FROM Table1 GROUP BY id) as t2 ON
         t1.timedate = t2.firsttimedate
 GROUP BY t1.timedate