求一条SQL语句,按日期统计，没有数据显示为0

zjf1119 2014-05-29 10:27:18

A表
日期金额
2014-05-01 100
2014-05-02 200
2014-05-05 300
2014-05-06 200

需要显示出来的是这样
2014-05-01 100
2014-05-02 200
2014-05-03 0
2014-05-04 0
2014-05-05 300
2014-05-06 200

请各位大侠帮帮我

...全文

7169 9 打赏收藏转发到动态举报

写回复

用AI写文章

9 条回复

切换为时间正序

请发表友善的回复…

发表回复

dongsheng10101 2016-04-18

打赏
举报

引用 1 楼 fredrickhu 的回复:

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2014-05-29 10:32:27
-- Version:
--      Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86) 
--	Feb 10 2012 19:13:17 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition: Core-based Licensing on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[A]
if object_id('[A]') is not null drop table [A]
go 
create table [A]([日期] datetime,[金额] int)
insert [A]
select '2014-05-01',100 union all
select '2014-05-02',200 union all
select '2014-05-05',300 union all
select '2014-05-06',200
--------------开始查询--------------------------
select b.日期,isnull(a.金额,0) as 金额
from
(
select convert(varchar(10),dateadd(day,number,'2014-05-01'),120)  as 日期
from
    master..spt_values 
where 
    datediff(day,dateadd(day,number,'2014-05-01'), '2014-05-06')>=0
    and number>=0 
    and type='p')b
left join a on a.日期=b.日期
----------------结果----------------------------
/* 日期         金额
---------- -----------
2014-05-01 100
2014-05-02 200
2014-05-03 0
2014-05-04 0
2014-05-05 300
2014-05-06 200

(6 行受影响)

*/

+1 用到master..spt_values表中number列数据（1－31），但个人建议，自己新建一张表num(sid int)，里面事先存放31条记录：分别为1－31的数据，这样更好。

道玄希言 2016-04-16

打赏
举报

一个另类的写法, 这么写日期没写死吧.... 呵呵


;with A(日期,金额) as
(                             
select '2014-05-01','100' union all
select '2014-05-02','200' union all
select '2014-05-05','300' union all
select '2014-05-06','200'
),
B(id) as 
(
    select 0 union all
    select 1 union all
    select 2 union all
    select 3 union all
    select 4 union all
    select 5 union all
    select 6 union all
    select 7 union all
    select 8 union all
    select 9 union all
	select 10 union all
    select 11 union all
    select 12 union all
    select 13 union all
    select 14 union all
    select 15 union all
    select 16 union all
    select 17 union all
    select 18 union all
    select 19 union all    
	select 20 union all
    select 21 union all
    select 22 union all
    select 23 union all
    select 24 union all
    select 25 union all
    select 26 union all
    select 27 union all
    select 28 union all
    select 29 union all
    select 30 union all
    select 31 
),
C(mindt, maxdt) as
(
    select Min(日期) as mindt,  Max(日期) as maxdt from A
)
select T.日期, ISNULL(A.金额, 0) as 金额 from
(
select convert(varchar(10),dateadd(day, id, (select mindt from C)),120) as 日期
from B
) as T
left join A 
on A.日期=T.日期
where T.日期 <= (select maxdt from C)

习惯性蹭分 2016-04-16

打赏
举报

2005以上版本，日期动态


use tempdb
go
if object_id('[A]') is not null drop table [A]
go 
create table [A]([日期] datetime,[金额] int)
insert [A]
select '2014-05-01',100 union all
select '2014-05-02',200 union all
select '2014-05-05',300 union all
select '2014-05-06',200
declare @stdate smalldatetime,
        @eddate smalldatetime
select  @stdate=min([日期]),
        @eddate=max([日期]) from [A]
;with sel as
(select @stdate as [date],1 as val
union all
select  dateadd(day,val,@stdate),val+1 as val
from sel 
where val<=datediff(day,@stdate,@eddate)
)
select sel.[date] as [日期],sum(isnull(A.[金额],0)) as [金额] from sel
left join [A] 
on sel.[date]=[A].[日期]
group by sel.[date]
option(maxrecursion 0)

fishparadise 2016-04-16

打赏
举报

其实我很想帮LZ，但看到字段使用中文。。。

baidu_28406303 2016-04-15

打赏
举报

if object_id('[A]') is not null drop table [A] go create table [A]([日期] datetime,[金额] int) insert [A] select '2014-05-01',100 union all select '2014-05-02',200 union all select '2014-05-05',300 union all select '2014-05-06',200 --------------开始查询-------------------------- select b.日期,isnull(a.金额,0) as 金额 from ( select convert(varchar(10),dateadd(day,number,'2014-05-01'),120) as 日期 from master..spt_values where datediff(day,dateadd(day,number,'2014-05-01'), '2014-05-06')>=0 and number>=0 and type='p')b left join a on a.日期=b.日期 ----------------结果---------------------------- /* 日期金额 ---------- ----------- 2014-05-01 100 2014-05-02 200 2014-05-03 0 2014-05-04 0 2014-05-05 300 2014-05-06 200 (6 行受影响) 你们这样写，日期不是写死了么？？应该先取到最大的日期和最小的日期再，，，想学习下，有大神指导下么

飞啊子 2014-05-29

打赏
举报



--让我喝点汤，以下是 Nums表.

PRINT 'nums表 插入数据. begin '

CREATE TABLE Nums(Rn int NOT NULL);
GO

 ;WITH 
 t1 AS (SELECT 1 AS c UNION ALL SELECT 1 ) --SELECT * FROM t1;
,t2 AS (SELECT 1 AS c FROM t1 AS a,t1 AS b) --SELECT * FROM t2;
,t3 AS (SELECT 1 AS c FROM t2 AS a,t2 AS b) --SELECT * FROM t3;
,t4 AS (SELECT 1 AS c FROM t3 AS a,t3 AS b) --SELECT * FROM t4;
,t5 AS (SELECT 1 AS c FROM t4 AS a,t4 AS b) --SELECT * FROM t5;
,t6 AS (SELECT 1 AS c FROM t5 AS a,t5 AS b) --SELECT * FROM t6;
,tNums 
AS(
	SELECT ROW_NUMBER() OVER(ORDER BY t6.c) AS Rn FROM t6
)INSERT INTO Nums (Rn)  SELECT *  FROM tnums t WHERE t.rn<=1000000;
 
GO
ALTER TABLE Nums ADD CONSTRAINT PK_Nums_Rn  PRIMARY KEY CLUSTERED(Rn);
GO

PRINT 'nums表 插入数据. end '

GO

--小F-- 2014-05-29

打赏
举报

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2014-05-29 10:32:27
-- Version:
--      Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86) 
--	Feb 10 2012 19:13:17 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition: Core-based Licensing on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[A]
if object_id('[A]') is not null drop table [A]
go 
create table [A]([日期] datetime,[金额] int)
insert [A]
select '2014-05-01',100 union all
select '2014-05-02',200 union all
select '2014-05-05',300 union all
select '2014-05-06',200
--------------开始查询--------------------------
select b.日期,isnull(a.金额,0) as 金额
from
(
select convert(varchar(10),dateadd(day,number,'2014-05-01'),120)  as 日期
from
    master..spt_values 
where 
    datediff(day,dateadd(day,number,'2014-05-01'), '2014-05-06')>=0
    and number>=0 
    and type='p')b
left join a on a.日期=b.日期
----------------结果----------------------------
/* 日期         金额
---------- -----------
2014-05-01 100
2014-05-02 200
2014-05-03 0
2014-05-04 0
2014-05-05 300
2014-05-06 200

(6 行受影响)

*/

--小F-- 2014-05-29

打赏
举报

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2014-05-29 10:32:27
-- Version:
--      Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86) 
--	Feb 10 2012 19:13:17 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition: Core-based Licensing on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[A]
if object_id('[A]') is not null drop table [A]
go 
create table [A]([日期] datetime,[金额] int)
insert [A]
select '2014-05-01',100 union all
select '2014-05-02',200 union all
select '2014-05-05',300 union all
select '2014-05-06',200
--------------开始查询--------------------------
select b.日期,isnull(a.金额,0) as 金额
from
(
select convert(varchar(10),dateadd(day,number,'2014-05-01'),120)  as 日期
from
    master..spt_values 
where 
    datediff(day,dateadd(day,number,'2014-05-01'), '2014-05-06')>=0
    and number>=0 
    and type='p')b
left join a on a.日期=b.日期
----------------结果----------------------------
/* 日期         金额
---------- -----------
2014-05-01 100
2014-05-02 200
2014-05-03 0
2014-05-04 0
2014-05-05 300
2014-05-06 200

(6 行受影响)

*/

以学习为目的 2014-05-29

打赏
举报

思路就是找出空缺日期，然后在和原表关联

引用 2 楼 fredrickhu 的回复:

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2014-05-29 10:32:27
-- Version:
--      Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86) 
--	Feb 10 2012 19:13:17 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition: Core-based Licensing on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[A]
if object_id('[A]') is not null drop table [A]
go 
create table [A]([日期] datetime,[金额] int)
insert [A]
select '2014-05-01',100 union all
select '2014-05-02',200 union all
select '2014-05-05',300 union all
select '2014-05-06',200
--------------开始查询--------------------------
select b.日期,isnull(a.金额,0) as 金额
from
(
select convert(varchar(10),dateadd(day,number,'2014-05-01'),120)  as 日期
from
    master..spt_values 
where 
    datediff(day,dateadd(day,number,'2014-05-01'), '2014-05-06')>=0
    and number>=0 
    and type='p')b
left join a on a.日期=b.日期
----------------结果----------------------------
/* 日期         金额
---------- -----------
2014-05-01 100
2014-05-02 200
2014-05-03 0
2014-05-04 0
2014-05-05 300
2014-05-06 200

(6 行受影响)

*/