表A
CustomerID CreateTime Order
1001 2007-1-1 1
1001 2007-1-2 2
1001 2007-1-3 8
1002 2007-1-2 3
1003 2007-1-3 4
1004 2007-1-4 1查询输入参数 BeginDate EndDate
期望结果:统计出在这个时间段客户的Order数量(PeriodOrder),
和所有的Order数量(TotalOrder)。
如,当BeginDate = 2007-1-1 EndDate = 2007-1-2 时,返回如下结果:
CustomerID PeriodOrder TotalOrder
1001 3 11
1002 3 3
1003 0 4
1004 0 1
CustomerID CreateTime Order
1001 2007-1-1 1
1001 2007-1-2 2
1001 2007-1-3 8
1002 2007-1-2 3
1003 2007-1-3 4
1004 2007-1-4 1查询输入参数 BeginDate EndDate
期望结果:统计出在这个时间段客户的Order数量(PeriodOrder),
和所有的Order数量(TotalOrder)。
如,当BeginDate = 2007-1-1 EndDate = 2007-1-2 时,返回如下结果:
CustomerID PeriodOrder TotalOrder
1001 3 11
1002 3 3
1003 0 4
1004 0 1
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 sum([Order]) from #t where CustomerID=a.CustomerID and CreateTime>=@rq1 and CreateTime<=@rq2),0) as PeriodOrder ,
isnull((select sum([Order]) from #t where CustomerID=a.CustomerID),0) as TotalOrder
from #t as A
group by CustomerID
drop table #t
/*CustomerID PeriodOrder TotalOrder
1001 3 11
1002 3 3
1003 0 4
1004 0 1*/
go
insert A values('1001','2007-1-1',1)
insert A values('1001','2007-1-2',2)
insert A values('1001','2007-1-3',8)
insert A values('1002','2007-1-2',3)
insert A values('1003','2007-1-3',4)
insert A values('1004','2007-1-4',1)go
select * from A
goselect CustomerID,[PeriodOrder]=count(Ordern),[TotalOrder]=sum(Ordern)
from A
group by CustomerID
godrop table A
go
go
insert A values('1001','2007-1-1',1)
insert A values('1001','2007-1-2',2)
insert A values('1001','2007-1-3',8)
insert A values('1002','2007-1-2',3)
insert A values('1003','2007-1-3',4)
insert A values('1004','2007-1-4',1)go
select * from A
goselect CustomerID,[PeriodOrder]=count(Ordern),[TotalOrder]=sum(Ordern)
from A
where CreateTime between '2007-1-1' and '2007-1-2'
group by CustomerID
godrop table A
go
go
create table A(CustomerID varchar(10),CreateTime datetime,[Order] int)
insert into A
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',1select a.CustomerID,isnull(b.PeriodOrder,0) as PeriodOrder,a.TotalOrder
from (select CustomerID,sum([Order]) as TotalOrder from A group by CustomerID)a
left join (select CustomerID,sum([Order]) as PeriodOrder from A where CreateTime between '2007-01-01' and '2007-01-02' group by CustomerID)b on a.CustomerID=b.CustomerID
/*
CustomerID PeriodOrder TotalOrder
---------- ----------- -----------
1001 3 11
1002 3 3
1003 0 4
1004 0 1(所影响的行数为 4 行)
*/
insert into tb
select 1001, '2007-01-01', 1 union all
select 1001, '2007-01-02', 2 union all
select 1001, '2007-01-03', 8 union all
select 1002, '2007-01-02', 3 union all
select 1003, '2007-01-03', 4 union all
select 1004, '2007-01-04', 1 declare @dt1 datetime, @dt2 datetime
set @dt1 = '2007-01-01'
set @dt2 = '2007-01-02'select id as CustomerID, (Select Sum([order]) from tb a
where ctime between @dt1 and @dt2
and id = b.id
group by id) as PeriodOrder,
sum([order]) as TotalOrder
from tb b
group by iddrop table tb
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 sum([Order]) from #t where CustomerID=a.CustomerID and CreateTime>=@rq1 and CreateTime<=@rq2),0) as PeriodOrder ,
isnull(sum([Order]),0) as TotalOrder
from #t as A
group by CustomerID
drop table #t
/*CustomerID PeriodOrder TotalOrder
1001 3 11
1002 3 3
1003 0 4
1004 0 1*/
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*/