如何查询每天新增的用户

Question

userid  createDate
------  ----------
AAAA    2009-06-01
AAAA    2009-06-01
BBBB    2009-06-01
BBBB    2009-06-02
BBBB    2009-06-02
CCCC    2009-06-02
AAAA    2009-06-03
BBBB    2009-06-03
CCCC    2009-06-03
DDDD    2009-06-03如何查询每天新增的用户得到结果createDate  newUserCount
----------  -----------
2009-06-01  2
2009-06-02  2
2009-06-03  1

Answer 1

更正一下，结果应该是
createDate  newUserCount 
----------  ----------- 
2009-06-01  2 
2009-06-02  1 
2009-06-03  1 

Answer 2

select createDate,newUserCount=count(distinct userid) from tb 
group by createDate
order by createDate

Answer 3

select 
  convert(varchar(10),createDate,120) as createDate,
  count(distinct userid) as newUserCount
from
  tb
group by
  convert(varchar(10),createDate,120)

Answer 4

select aa.createDate,newUserCount=count(distinct aa.userid) from 
(select * from tb a where not exists(select 1 from tb where userid=a.userid and createDate<a.createDate)) aa
group by aa.createDate
order by aa.createDate