IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t
;
create table #t(id int identity,Dt varchar(10))
;
declare @starttime datetime,@endtime datetime
set @starttime = '===start==='
set @endtime ='===end==='
insert #t
select convert(varchar(10),dateadd(day,number,@starttime),112) dt
from master..spt_values
where type='P' and number between 0 and datediff(day,@starttime,@endtime)
select ISNULL(某一段时间段的订单量,0) ordernum,isnull(成交的订单金额,0) ordermoney,#t.Dt from (
select CONVERT(varchar(10),PayTime,112) dt,
COUNT(ID) 某一段时间段的订单量,
SUM(PayPrice) 成交的订单金额 from TB_Order
where [State] in (1,2,3,7,8) and PayTime>='===start=== 00:00:01' and PayTime<='===end=== 23:59:59'
group by CONVERT(varchar(10),PayTime,112)
) tt
right join #t on #t.Dt=tt.dt
