27,579
社区成员
发帖
与我相关
我的任务
分享
use Tempdb
go
--> -->
if not object_id(N'Tempdb..#Order') is null
drop table #Order
Go
Create table #Order([id] int,[amount] int,[createTime] Datetime)
Insert #Order
select 1,10,'2016-8-23 08:00:11' union all
select 2,10,'2016-8-25 08:00:11' union all
select 3,10,'2016-8-26 08:00:11' union all
select 4,10,'2016-8-27 08:00:11' union all
select 5,10,'2016-8-29 08:00:11'
GO
--传参
DECLARE @Dt DATE='2016-08-29'
,@Day INT=30 --7天前/30天
;WITH CTEDT
AS
(
SELECT @Dt AS DT
UNION ALL
SELECT DATEADD(dd,-1,DT) FROM CTEDT WHERE DT>DATEADD(dd,-@Day+1,@Dt)
)
SELECT a.DT,ISNULL(SUM([amount]),0) AS [amount] FROM CTEDT AS a LEFT JOIN #Order AS b ON a.DT=CONVERT(VARCHAR(10),[createTime],120) GROUP BY a.DT ORDER BY a.DT DESC
/*
DT amount
2016-08-29 10
2016-08-28 0
2016-08-27 10
2016-08-26 10
2016-08-25 10
2016-08-24 0
2016-08-23 10
2016-08-22 0
2016-08-21 0
2016-08-20 0
2016-08-19 0
2016-08-18 0
2016-08-17 0
2016-08-16 0
2016-08-15 0
2016-08-14 0
2016-08-13 0
2016-08-12 0
2016-08-11 0
2016-08-10 0
2016-08-09 0
2016-08-08 0
2016-08-07 0
2016-08-06 0
2016-08-05 0
2016-08-04 0
2016-08-03 0
2016-08-02 0
2016-08-01 0
2016-07-31 0*/
use Tempdb
go
--> -->
if not object_id(N'Tempdb..#Order') is null
drop table #Order
Go
Create table #Order([id] int,[amount] int,[createTime] Datetime)
Insert #Order
select 1,10,'2016-8-23 08:00:11' union all
select 2,10,'2016-8-25 08:00:11' union all
select 3,10,'2016-8-26 08:00:11' union all
select 4,10,'2016-8-27 08:00:11' union all
select 5,10,'2016-8-29 08:00:11'
GO
DECLARE @Dt DATE=GETDATE()
;WITH CTEDT
AS
(
SELECT @Dt AS DT
UNION ALL
SELECT DATEADD(dd,-1,DT) FROM CTEDT WHERE DT>DATEADD(dd,-6,@Dt)
)
SELECT a.DT,ISNULL(SUM([amount]),0) AS [amount] FROM CTEDT AS a LEFT JOIN #Order AS b ON a.DT=CONVERT(VARCHAR(10),[createTime],120) GROUP BY a.DT ORDER BY a.DT DESC
/*
DT amount
2016-08-29 10
2016-08-28 0
2016-08-27 10
2016-08-26 10
2016-08-25 10
2016-08-24 0
2016-08-23 10*/
use Tempdb
go
--> -->
if not object_id(N'Tempdb..#Order') is null
drop table #Order
Go
Create table #Order([id] int,[amount] int,[createTime] Datetime)
Insert #Order
select 1,10,'2016-8-23 08:00:11' union all
select 2,10,'2016-8-25 08:00:11' union all
select 3,10,'2016-8-26 08:00:11' union all
select 4,10,'2016-8-27 08:00:11' union all
select 5,10,'2016-8-29 08:00:11'
GO
DECLARE @Dt DATE=GETDATE()
;WITH CTEDT
AS
(
SELECT @Dt AS DT
UNION ALL
SELECT DATEADD(dd,-1,DT) FROM CTEDT WHERE DT>DATEADD(dd,-6,@Dt)
)
SELECT a.DT,ISNULL(SUM([amount]),0) AS [amount] FROM CTEDT AS a LEFT JOIN #Order AS b ON a.DT=CONVERT(VARCHAR(10),[createTime],120) GROUP BY a.DT
/*
DT amount
2016-08-23 10
2016-08-24 0
2016-08-25 10
2016-08-26 10
2016-08-27 10
2016-08-28 0
2016-08-29 10*/