日期 销售额
2017/01/01 100
2017/01/05 200
2017/02/01 200
2017/04/01 222
2017/04/08 233我想要的汇总样式是:2017/01 300
2017/02 200
2017/03 0
2017/04 455能不能再不引入临时表的情况下实现?谢谢了。
2017/01/01 100
2017/01/05 200
2017/02/01 200
2017/04/01 222
2017/04/08 233我想要的汇总样式是:2017/01 300
2017/02 200
2017/03 0
2017/04 455能不能再不引入临时表的情况下实现?谢谢了。
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([日期] Date,[销售额] int)
Insert #T
select '2017/01/01',100 union all
select '2017/01/05',200 union all
select '2017/02/01',200 union all
select '2017/04/01',222 union all
select '2017/04/08',233
Go
--测试数据结束
SELECT a.alldate ,
ISNULL(SUM(销售额), 0) AS 销售额
FROM ( SELECT DATEADD(MONTH, number, CONVERT(DATE, '2017-01-01')) alldate
FROM master.dbo.spt_values
WHERE type = 'P'
AND number BETWEEN 0 AND 12 --根据需要调整
) a
LEFT JOIN #T b ON DATEDIFF(MONTH, a.alldate, b.日期) = 0
GROUP BY alldate
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([日期] Date,[销售额] int)
Insert #T
select '2017/01/01',100 union all
select '2017/01/05',200 union all
select '2017/02/01',200 union all
select '2017/04/01',222 union all
select '2017/04/08',233
Go
--测试数据结束
;WITH cte AS (
SELECT MIN(t.日期) AS startdate,MAX(t.日期) AS enddate FROM #T AS t
)
,cte1 AS (
SELECT startdate AS date1 FROM cte
UNION ALL
SELECT DATEADD(day,1,c.date1) AS date1 FROM cte1 AS c WHERE c.date1<(select enddate FROM cte)
)
SELECT CAST(date1 AS VARCHAR(7)),SUM(isnull(t.[销售额],0))
FROM cte1 AS c
left JOIN #T AS t ON c.date1=t.日期
GROUP BY CAST(date1 AS VARCHAR(7))