表A
CustomerID CreateTime OrderID
1001 2007-1-1 01
1001 2007-1-2 02
1001 2007-1-3 03
1002 2007-1-2 04
1003 2007-1-3 05
1004 2007-1-4 06查询输入参数 BeginDate EndDate
期望结果:统计出在这个时间段客户的Order记录条数(PeriodOrder),
和所有的Order记录条数(TotalOrder)。
如,当BeginDate = 2007-1-1 EndDate = 2007-1-2 时,返回如下结果:
CustomerID PeriodOrder TotalOrder
1001 2 3
1002 1 1
1003 0 1
1004 0 1
CustomerID CreateTime OrderID
1001 2007-1-1 01
1001 2007-1-2 02
1001 2007-1-3 03
1002 2007-1-2 04
1003 2007-1-3 05
1004 2007-1-4 06查询输入参数 BeginDate EndDate
期望结果:统计出在这个时间段客户的Order记录条数(PeriodOrder),
和所有的Order记录条数(TotalOrder)。
如,当BeginDate = 2007-1-1 EndDate = 2007-1-2 时,返回如下结果:
CustomerID PeriodOrder TotalOrder
1001 2 3
1002 1 1
1003 0 1
1004 0 1
go
create table A(CustomerID varchar(10),CreateTime datetime,OrderID varchar(10))
insert into A
select '1001','2007-1-1','01'
union all select '1001','2007-1-2','02'
union all select '1001','2007-1-3','03'
union all select '1002','2007-1-2','04'
union all select '1003','2007-1-3','05'
union all select '1004','2007-1-4','06'select CustomerID,sum(case when CreateTime between '2007-01-01' and '2007-01-02' then 1 else 0 end) as PeriodOrder,count(1) as TotalOrder
from A
group by CustomerID
/*
CustomerID PeriodOrder TotalOrder
---------- ----------- -----------
1001 2 3
1002 1 1
1003 0 1
1004 0 1(所影响的行数为 4 行)
*/
insert into #t
select 1001,'2007-1-1',1 union all
select 1001,'2007-1-2',2 union all
select 1001,'2007-1-3',8 union all
select 1002,'2007-1-2',3 union all
select 1003,'2007-1-3',4 union all
select 1004,'2007-1-4',1
declare @rq1 datetime, @rq2 datetimeset @rq1='2007-1-1'set @rq2='2007-1-2'
select
CustomerID,
isnull((select count(*) from #t where CustomerID=a.CustomerID and CreateTime>=@rq1 and CreateTime<=@rq2),0) as PeriodOrder ,
count([Order]) as TotalOrder
from #t as A
group by CustomerID
drop table #t
/*CustomerID PeriodOrder TotalOrder
1001 2 3
1002 1 1
1003 0 1
1004 0 1*/
insert into #t
select 1001,'2007-1-1','01' union all
select 1001,'2007-1-2','02' union all
select 1001,'2007-1-3','03' union all
select 1002,'2007-1-2','04' union all
select 1003,'2007-1-3','05' union all
select 1004,'2007-1-4','06'
declare @rq1 datetime, @rq2 datetimeset @rq1='2007-1-1'set @rq2='2007-1-2'
select
CustomerID,
isnull((select count(*) from #t where CustomerID=a.CustomerID and CreateTime>=@rq1 and CreateTime<=@rq2),0) as PeriodOrder ,
count([Order]) as TotalOrder
from #t as A
group by CustomerID
drop table #t
/*CustomerID PeriodOrder TotalOrder
1001 2 3
1002 1 1
1003 0 1
1004 0 1*/