34,838
社区成员




USE tempdb
GO
IF OBJECT_ID('dbo.[t]') IS NOT NULL
DROP TABLE dbo.[t]
GO
CREATE TABLE dbo.[t](
[日期] DATE
,[吨数] INT
)
GO
SET NOCOUNT ON
INSERT INTO dbo.[t] VALUES(N'2012/1/1',N'3')
INSERT INTO dbo.[t] VALUES(N'2012/1/4',N'5')
INSERT INTO dbo.[t] VALUES(N'2012/1/5',N'3')
INSERT INTO dbo.[t] VALUES(N'2012/1/5',N'7')
INSERT INTO dbo.[t] VALUES(N'2012/1/7',N'3')
INSERT INTO dbo.[t] VALUES(N'2012/1/12',N'7')
INSERT INTO dbo.[t] VALUES(N'2012/1/31',N'31')
---- 以上为测试表及测试数据 --------
SELECT [日期],SUM(t.吨数) AS 吨数
FROM t
GROUP BY [日期]
ORDER BY t.[日期]
/*
日期 吨数
---------- -----------
2012-01-01 3
2012-01-04 5
2012-01-05 10
2012-01-07 3
2012-01-12 7
2012-01-31 31
*/
USE tempdb
GO
IF OBJECT_ID('dbo.[t]') IS NOT NULL
DROP TABLE dbo.[t]
GO
CREATE TABLE dbo.[t](
[日期] DATE
,[吨数] INT
)
GO
SET NOCOUNT ON
INSERT INTO dbo.[t] VALUES(N'2012/1/1',N'3')
INSERT INTO dbo.[t] VALUES(N'2012/1/4',N'5')
INSERT INTO dbo.[t] VALUES(N'2012/1/5',N'3')
INSERT INTO dbo.[t] VALUES(N'2012/1/5',N'7')
INSERT INTO dbo.[t] VALUES(N'2012/1/7',N'3')
INSERT INTO dbo.[t] VALUES(N'2012/1/12',N'7')
INSERT INTO dbo.[t] VALUES(N'2012/1/31',N'31')
---- 以上为测试表及测试数据 --------
;WITH cte AS (
SELECT [日期],SUM(t.吨数) AS 吨数
FROM t
GROUP BY [日期]
)
,seq AS(
--6365529,六百三十万,够用了
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1))-1 AS number
FROM (
SELECT 1 AS id FROM [master].dbo.spt_values AS s1
CROSS APPLY [master].dbo.spt_values AS s2
) AS tt
)
,cte2 AS(
SELECT DISTINCT DATEADD(DAY,sv.number,(SELECT min(日期) FROM cte)) AS [日期]
FROM seq AS sv
OUTER APPLY cte
WHERE sv.number<= DATEDIFF(DAY,(SELECT min(日期) FROM cte ),(SELECT max(日期) FROM cte))
)
SELECT cte2.[日期],isnull(cte.[吨数],0) AS [吨数]
FROM cte2
LEFT JOIN cte ON cte2.[日期]=cte.[日期]
ORDER BY [日期]
/*
日期 吨数
---------- -----------
2012-01-01 3
2012-01-02 0
2012-01-03 0
2012-01-04 5
2012-01-05 10
2012-01-06 0
2012-01-07 3
2012-01-08 0
2012-01-09 0
2012-01-10 0
2012-01-11 0
2012-01-12 7
2012-01-13 0
2012-01-14 0
2012-01-15 0
2012-01-16 0
2012-01-17 0
2012-01-18 0
2012-01-19 0
2012-01-20 0
2012-01-21 0
2012-01-22 0
2012-01-23 0
2012-01-24 0
2012-01-25 0
2012-01-26 0
2012-01-27 0
2012-01-28 0
2012-01-29 0
2012-01-30 0
2012-01-31 31
*/
USE tempdb
GO
IF OBJECT_ID('dbo.[t]') IS NOT NULL
DROP TABLE dbo.[t]
GO
CREATE TABLE dbo.[t](
[日期] DATE
,[吨数] INT
)
GO
SET NOCOUNT ON
INSERT INTO dbo.[t] VALUES(N'2012/1/1',N'3')
INSERT INTO dbo.[t] VALUES(N'2012/1/4',N'5')
INSERT INTO dbo.[t] VALUES(N'2012/1/5',N'3')
INSERT INTO dbo.[t] VALUES(N'2012/1/5',N'7')
INSERT INTO dbo.[t] VALUES(N'2012/1/7',N'3')
INSERT INTO dbo.[t] VALUES(N'2012/1/12',N'7')
INSERT INTO dbo.[t] VALUES(N'2012/1/31',N'31')
---- 以上为测试表及测试数据 --------
;WITH cte AS (
SELECT [日期],SUM(t.吨数) AS 吨数
FROM t
GROUP BY [日期]
)
,cte2 AS(
SELECT DISTINCT sv.number, DATEADD(DAY,sv.number,(SELECT min(日期) FROM cte)) AS [日期]
FROM MASTER.dbo.spt_values AS sv
OUTER APPLY cte
WHERE sv.[type]='P'
AND sv.number<= DATEDIFF(DAY,(SELECT min(日期) FROM cte ),(SELECT max(日期) FROM cte))
)
SELECT cte2.[日期],isnull(cte.[吨数],0) AS [吨数]
FROM cte2
LEFT JOIN cte ON cte2.[日期]=cte.[日期]
ORDER BY [日期]
/*
日期 吨数
---------- -----------
2012-01-01 3
2012-01-02 0
2012-01-03 0
2012-01-04 5
2012-01-05 10
2012-01-06 0
2012-01-07 3
2012-01-08 0
2012-01-09 0
2012-01-10 0
2012-01-11 0
2012-01-12 7
2012-01-13 0
2012-01-14 0
2012-01-15 0
2012-01-16 0
2012-01-17 0
2012-01-18 0
2012-01-19 0
2012-01-20 0
2012-01-21 0
2012-01-22 0
2012-01-23 0
2012-01-24 0
2012-01-25 0
2012-01-26 0
2012-01-27 0
2012-01-28 0
2012-01-29 0
2012-01-30 0
2012-01-31 31
*/