订单表
订单编号 客户编号 订单时间
orderid customerid ordertime
1 1 2010-02-01 15:11:12
2 2 2010-02-01 18:12:12
3 1 2010-02-01 19:52:05
4 3 2010-02-02 12:11:12
5 1 2010-02-02 15:01:12
6 2 2010-02-02 09:31:22
7 2 2010-02-02 19:38:16
...同一天,同一个客户会有0-N个订单,
现在需要统计这个月每天有多少个客户下了订单查询结果
订单日期 客户数
2010-02-01 2
2010-02-02 3
...
订单编号 客户编号 订单时间
orderid customerid ordertime
1 1 2010-02-01 15:11:12
2 2 2010-02-01 18:12:12
3 1 2010-02-01 19:52:05
4 3 2010-02-02 12:11:12
5 1 2010-02-02 15:01:12
6 2 2010-02-02 09:31:22
7 2 2010-02-02 19:38:16
...同一天,同一个客户会有0-N个订单,
现在需要统计这个月每天有多少个客户下了订单查询结果
订单日期 客户数
2010-02-01 2
2010-02-02 3
...
convert(varchar(10),订单日期,120)订单日期,
count(distinct customerid)客户数
from 订单表
group by convert(varchar(10),订单日期,120)
if object_id('[tb]') is not null drop table [tb]
go
create table [tb] (orderid int,customerid int,ordertime datetime)
insert into [tb]
select 1,1,'2010-02-01 15:11:12' union all
select 2,2,'2010-02-01 18:12:12' union all
select 3,1,'2010-02-01 19:52:05' union all
select 4,3,'2010-02-02 12:11:12' union all
select 5,1,'2010-02-02 15:01:12' union all
select 6,2,'2010-02-02 09:31:22' union all
select 7,2,'2010-02-02 19:38:16'
select
convert(varchar(10),ordertime,120)订单日期,
count(distinct customerid)客户数
from tb
group by convert(varchar(10),ordertime,120)
/*
订单日期 客户数
---------- -----------
2010-02-01 2
2010-02-02 3(2 個資料列受到影響)
*/