34,587
社区成员
发帖
与我相关
我的任务
分享如何查询前12个月的数据，没有数据的自动填充为0
2016-9 0
2016-10 0
2016-11 100000.00
2017-01 0
2017-02 0
2017-03 0
2017-04 0
2017-05 6406.85
2017-06 196854.20
2017-07 1175842.28
2017-08 419287.10
2017-09 767780.14
上面是我想要查询出来的结果，但是为0的数据我数据库里是没有的，怎么在查询的时候给它填充进去，有哪位大神知道的，谢谢告知
...全文
请发表友善的回复…
发表回复
clark_kidd 2017-09-27
- 打赏
- 举报
CREATE FUNCTION [dbo].[f_Last12Mon]
(
@d datetime
)RETURNS @t TABLE
(
YM char(7)
)
AS
BEGIN
declare @i int
set @i=12
while @i>0
begin
insert into @t values(Left(Convert(nvarchar, Dateadd(m,-@i, @d), 120), 7))
set @i=@i-1
end
RETURN
END
GO
select a.*, ISNULL(YourTable.Amount, 0) as Amount from [f_Last12Mon](getdate()) a
Left Outer Join YourTable ON a.YM = YourTable.YM
ilovekworm 2017-09-10
- 打赏
- 举报
这些我一般用C之类的语言编程，SQL语言功能真的比较难搞
小野马1209 2017-09-07
- 打赏
- 举报
我遇到过一次这样的问题，已经把字段Null转换为0了，Case when 判断的时候，用0返回的结果不对，要用Null才可以
IsNull(Value,0)
case when value=0 then xxx ----结果不对
后面改成了 when value is null then ---这样写结果是对的
吉普赛的歌 版主 2017-09-07
- 打赏
- 举报
USE tempdb
GO
--测试数据
if not object_id(N'T') is null
drop table T
Go
Create table T([adate] nvarchar(27),[avalue] decimal(18,2))
Insert T
--select N'2016-9',null union all
--select N'2016-10',null union all
select N'2016-11',100000.00 union all
--select N'2017-01',null union all
--select N'2017-02',null union all
--select N'2017-03',null union all
select N'2017-04',0 union all
select N'2017-05',6406.85 union all
select N'2017-06',196854.20 union all
select N'2017-07',1175842.28 union all
select N'2017-08',419287.10 union all
select N'2017-09',767780.14
GO
SELECT t2.d,ISNULL(t.avalue,0) AS avalue
FROM (
--生成 2016-09 到 2017-09 的所有月份，这是关键了
SELECT CONVERT(CHAR(7),DATEADD(MONTH,sv.number,'2016-09-01'),120) AS d
FROM MASTER.dbo.spt_values AS sv WHERE sv.type='p' AND sv.number BETWEEN 0 AND 12
) AS t2 LEFT JOIN T ON t2.d=t.adate
/*
d avalue
2016-09 0.00
2016-10 0.00
2016-11 100000.00
2016-12 0.00
2017-01 0.00
2017-02 0.00
2017-03 0.00
2017-04 0.00
2017-05 6406.85
2017-06 196854.20
2017-07 1175842.28
2017-08 419287.10
2017-09 767780.14
*/
顺势而为1 2017-09-07
- 打赏
- 举报
是这样吗,楼主
if object_id('tempdb..#Tmp_Data') is not null
drop table #Tmp_Data
CREATE TABLE #Tmp_Data (
my_date Datetime,
my_Amount Decimal(13,2))
INSERT INTO #Tmp_Data
Select '2016-11-05',100000.00 union
Select '2017-05-11',6406.85 union
Select '2017-06-20',196854.20 union
Select '2017-07-15',1175842.28 union
Select '2017-08-11',419287.10 union
Select '2017-09-25',767780.14
Select (cast(year(a.My_Date) as varchar)+'-'+cast(month(a.my_date) as varchar)) as my_date,
my_Amount
From (
Select DateAdd(MONTH,sv.number,DateAdd(Year,-1,getdate())) as my_Date
FROM master.dbo.spt_values AS sv
WHERE sv.type='P' AND sv.number BETWEEN 1 AND 12
) a
Left Join #Tmp_Data b on year(a.my_date)=year(b.my_date) and month(a.my_date)=month(b.my_Date)
充满荆棘的路 2017-09-07
- 打赏
- 举报
这句是把临时表里的所有为null的修改成0，
我是想把那个年月的日期改成动态的，比如在过了一个月，就没有上一年的9月了，而是变成10月了[/quote]
没明白什么意思[/quote]
解决了大佬，谢谢你，我把临时表里写死的数据，换成了当前日期减去月数，
select dateadd(mm,-1,getdate()),419287.10 union all
二月十六 版主 2017-09-07
- 打赏
- 举报
这句是把临时表里的所有为null的修改成0，
我是想把那个年月的日期改成动态的，比如在过了一个月，就没有上一年的9月了，而是变成10月了[/quote]
没明白什么意思
充满荆棘的路 2017-09-07
- 打赏
- 举报
这句是把临时表里的所有为null的修改成0，
我是想把那个年月的日期改成动态的，比如在过了一个月，就没有上一年的9月了，而是变成10月了
二月十六 版主 2017-09-07
- 打赏
- 举报
直接执行这句……
UPDATE 表名 SET avalue=0 WHERE avalue IS NULL充满荆棘的路 2017-09-07
- 打赏
- 举报
我那个数据也是动态的，我不知道怎么把这个临时表里写死的年月换成我那个动态的，大佬，在指点指点
二月十六 版主 2017-09-07
- 打赏
- 举报
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([adate] nvarchar(27),[avalue] decimal(18,8))
Insert #T
select N'2016-9',null union all
select N'2016-10',null union all
select N'2016-11',100000.00 union all
select N'2017-01',null union all
select N'2017-02',null union all
select N'2017-03',null union all
select N'2017-04',0 union all
select N'2017-05',6406.85 union all
select N'2017-06',196854.20 union all
select N'2017-07',1175842.28 union all
select N'2017-08',419287.10 union all
select N'2017-09',767780.14
Go
--测试数据结束
UPDATE #T SET avalue=0 WHERE avalue IS NULL
SELECT * FROM #T充满荆棘的路 2017-09-07
- 打赏
- 举报
大佬，我怎么把我表里的字段加进去啊，我是菜鸟，有点看不懂你写的这个
二月十六 版主 2017-09-07
- 打赏
- 举报
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([adate] nvarchar(27),[avalue] decimal(18,8))
Insert #T
select N'2016-9',null union all
select N'2016-10',null union all
select N'2016-11',100000.00 union all
select N'2017-01',null union all
select N'2017-02',null union all
select N'2017-03',null union all
select N'2017-04',0 union all
select N'2017-05',6406.85 union all
select N'2017-06',196854.20 union all
select N'2017-07',1175842.28 union all
select N'2017-08',419287.10 union all
select N'2017-09',767780.14
Go
--测试数据结束
Select adate,ISNULL(avalue,0) AS avalue from #T
听雨停了 2017-09-07
- 打赏
- 举报
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([adate] nvarchar(27),[avalue] decimal(18,8))
Insert #T
select N'2016-09',15000 union all
select N'2016-11',100000.00 union all
select N'2017-05',6406.85 union all
select N'2017-06',196854.20 union all
select N'2017-07',1175842.28 union all
select N'2017-09',767780.14
GO
--测试数据结束
DECLARE @maxdate DATE,
@mindate DATE
SELECT @maxdate = MAX(CAST([adate] + '-01' AS DATE)),
@mindate = MIN(CAST([adate] + '-01' AS DATE))
FROM #t;
WITH cte AS(
SELECT DATEADD(MONTH, number, @mindate) AS [adate]
FROM MASTER..spt_values
WHERE TYPE = 'P'
AND DATEADD(MONTH, number, @mindate) <= @maxdate
)
SELECT LEFT(a.[adate], 7) AS adate,
ISNULL(b.avalue, 0) AS avalue
FROM cte a
LEFT OUTER JOIN #t b
ON CAST(b.[adate] + '-01' AS DATE) = a.[adate]
----------------------------------------------------------------------------------------------
adate avalue
-------------- ---------------------------------------
2016-09 15000.00000000
2016-10 0.00000000
2016-11 100000.00000000
2016-12 0.00000000
2017-01 0.00000000
2017-02 0.00000000
2017-03 0.00000000
2017-04 0.00000000
2017-05 6406.85000000
2017-06 196854.20000000
2017-07 1175842.28000000
2017-08 0.00000000
2017-09 767780.14000000
(13 行受影响)
alex259 2017-09-07
- 打赏
- 举报
直接在case when 中用格式化语句 case when IsNull(Value,0) = 0 then xxx
