求助不连贯日期要变成连贯日期后显示出来

wx9288 2020-03-06 09:50:16
有一个表 A
日期 吨数
2012/1/1 3
2012/1/4 5
2012/1/5 3
2012/1/5 7
2012/1/7 3
2012/1/12 7
。。。。
2012/1/31 31


要按连贯日期 来得出每天的吨数
需要这样结果
日期 吨数
2012/1/1 3
2012/1/2 0
2012/1/3 0
2012/1/4 5
2012/1/5 10
。。。。。
2012/1/31 31


但不知道怎么弄,请问要如何解决,有比较简单办法吗?



...全文
171 8 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
8 条回复
切换为时间正序
请发表友善的回复…
发表回复
wx9288 2020-03-07
  • 打赏
  • 举报
回复
引用 7 楼 吉普赛的歌 的回复:
如果你的日期跨度非常大, master.dbo.spt_value 可能不够用, 因为只有 2048 个。 换成下面这种就完全没问题了:
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
*/
谢谢 @吉普赛的歌 版主的指导。我终于理解怎么做了
吉普赛的歌 版主 2020-03-07
  • 打赏
  • 举报
回复
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
 */
吉普赛的歌 版主 2020-03-07
  • 打赏
  • 举报
回复
如果你的日期跨度非常大, master.dbo.spt_value 可能不够用, 因为只有 2048 个。 换成下面这种就完全没问题了:
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
*/
吉普赛的歌 版主 2020-03-07
  • 打赏
  • 举报
回复
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
*/
Seagull16 2020-03-07
  • 打赏
  • 举报
回复
SELECT D.[FullDateAlternateKey] AS [日期] CASE WHEN A.[吨数] IS NULL THEN 0 ELSE A.[吨数] END AS [吨数] FROM [AdventureWorksDW2014].[dbo].[DimDate] AS D LEFT JOIN YourTable AS A ON CAST(A.[日期] AS DATE) = CAST(D.[FullDateAlternateKey] AS DATE)
Seagull16 2020-03-07
  • 打赏
  • 举报
回复
用你的这个表和一个时间表(DimDate, containing 日期)join:例如: SELECT D.[FullDateAlternateKey] AS [日期] ,A.[吨数] FROM [AdventureWorksDW2014].[dbo].[DimDate] AS D LEFT JOIN YourTable AS A ON CAST(A.[日期] AS DATE) = CAST(D.[FullDateAlternateKey] AS DATE)
wx9288 2020-03-07
  • 打赏
  • 举报
回复
引用 1 楼 Seagull16 的回复:
用你的这个表和一个时间表(DimDate, containing 日期)join:例如:

SELECT D.[FullDateAlternateKey] AS [日期]
,A.[吨数]
FROM [AdventureWorksDW2014].[dbo].[DimDate] AS D
LEFT JOIN YourTable AS A
ON CAST(A.[日期] AS DATE) = CAST(D.[FullDateAlternateKey] AS DATE)



请问这时间表是不是得自己创建一个?如果我需要计算2012年的,就需要创建应2012年时间表,如果要计算2019年,就需要首先创建一个2019年时间表吗?
wx9288 2020-03-07
  • 打赏
  • 举报
回复
引用 3 楼 吉普赛的歌 的回复:
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
*/


你好,你哦可能误会我意思,我需要的是2012-01-02 0 2012-01-03 0 这些表里面没有日期也显示出来

34,838

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧