根据自定义日期 分组查询

[claymore1114]

Order表结构：
id, amount, createTime
1 10 2016-8-23 08:00:11
2 10 2016-8-25 08:00:11
3 10 2016-8-26 08:00:11
4 10 2016-8-27 08:00:11
5 10 2016-8-29 08:00:11

需求是 ：
1.根据设定的时间段分成7组，比如统计最近7天的 交易额，今天是2016-8-29，分7组，即按照 2016-8-29、2016-8-28、2016-8-27、2016-8-26、2016-8-25、2016-8-24、2016-8-23 这7个时间 进行分组（匹配Order的createTime）处理订单。
如 输出，
时间 交易额
2016-8-29 10
2016-8-28 0
2016-8-27 10
2016-8-26 10
2016-8-25 10
2016-8-24 0
2016-8-23 10

2. 前面是最近7天，现在按照最近30天统计，也是分7组，跟上面一样，30天按照 30/7=4 天算，取整，即日期 2016-8-29、2016-8-25、2016-8-21、2016-8-17、2016-8-13、2016-8-9、2016-8-5.
只统计7个点的订单，如：
2016-8-21 ：统计是2016-8-21 这一天的订单
2016-8-17 ： 统计的是 2016-8-17这一天的订单

3. 7天、30天 可以 自定义天数的。

这个查询 怎么写，谢谢！

[claymore1114]

引用 3 楼 roy_88 的回复:

--传参 日期+天数

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

/*
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*/

[中国风]

加排序

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='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*/