本文介绍了一种使用SQL生成连续日期序列的方法，并通过示例展示了如何根据这些连续日期来汇总财务数据，包括收入、支出及余额等关键指标。
得到连续日期需要借助一个有连续序号的表,参考如何得到连续序号
--〉生成连续日期的方法
IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t
GO
create table #t(id int identity,Dt varchar(10))
go
declare @starttime datetime,@endtime datetime
set @starttime = '2010-5-01'
set @endtime ='2010-5-31'
insert #t
select convert(varchar(10),dateadd(day,number,@starttime),120) dt
from master..spt_values
where type='P' and number between 0 and datediff(day,@starttime,@endtime)
--结果
select * from #t
/*
id Dt
----------- ----------
1 2010-05-01
2 2010-05-02
3 2010-05-03
4 2010-05-04
5 2010-05-05
6 2010-05-06
7 2010-05-07
8 2010-05-08
9 2010-05-09
10 2010-05-10
11 2010-05-11
12 2010-05-12
13 2010-05-13
14 2010-05-14
15 2010-05-15
16 2010-05-16
17 2010-05-17
18 2010-05-18
19 2010-05-19
20 2010-05-20
21 2010-05-21
22 2010-05-22
23 2010-05-23
24 2010-05-24
25 2010-05-25
26 2010-05-26
27 2010-05-27
28 2010-05-28
29 2010-05-29
30 2010-05-30
31 2010-05-31
(31 行受影响)
*/
实际例子：
原帖：
http://topic.youkuaiyun.com/u/20100619/18/3b4d60f2-b477-414f-adec-569ccee3fec6.html
http://topic.youkuaiyun.com/u/20100619/15/367ed306-b8ea-4b2b-9614-2acd64c0e07e.html
根据开始时间，结束时间 生成连续的时间
然后在用这个连续的时间去比较表里的数据
if object_id('TZ_Money')is not null drop table TZ_Money go
create table TZ_Money
(
Id int identity(1,1) primary key,
Uid varchar(8) not null, --用户ID
Income numeric(10,2) not null, --收入
Expenditure numeric(10,2) not null, --支出
Balance numeric(10,2) not null, --余额
[Time] datetime not null, --日期(天为单位)
LastTime datetime null --最后时间
)
go
set identity_insert TZ_Money on
insert TZ_Money (id,uid,Income,Expenditure,Balance,Time)
select 1 ,'abc1', 1.00 ,5.00 ,96 ,'2010-06-7 12:12:12' union all
select 2 ,'abc1', 3.00 ,4.00, 95 ,'2010-06-7 21:12:45' union all
select 3 ,'abc1', 4.00 ,50.00 ,49 ,'2010-08-7 09:45:59'
go
if object_id('p_test1')is not null drop proc p_test1
go
create proc p_test1 @starttime datetime,@endtime datetime,@type nvarchar(4)='年',@uid varchar(8)
as
begin
declare @sql varchar(8000),@w varchar(1000),@g varchar(1000)
create table #t(id int identity,D varchar(10))
set @sql='select isnull(sum(Income),0.00) 收入,isnull(sum(Expenditure),0.00)支出,'
if @type='年'
begin
insert #t
select convert(varchar(4),dateadd(year,number,@starttime),120)
from master..spt_values
where type='P' and number between 0 and datediff(year,@starttime,@endtime)
set @sql=@sql+'isnull((select top 1 Balance from TZ_Money where convert(varchar(4),[Time],120)<=a.d and Balance is not null order by time desc),0.00) 余额,a.d as [time]'
set @sql=@sql+' from #t a left join TZ_Money t on a.d=convert(varchar(4),t.[Time],120) '
end
if @type='月'
begin
insert #t
select convert(varchar(7),dateadd(month,number,@starttime),120)
from master..spt_values
where type='P' and number between 0 and datediff(month,@starttime,@endtime)
set @sql=@sql+'isnull((select top 1 Balance from TZ_Money where convert(varchar(7),[Time],120)<=a.d and Balance is not null order by time desc),0.00) 余额,a.d as [time]'
set @sql=@sql+' from #t a left join TZ_Money t on a.d=convert(varchar(7),t.[Time],120) '
end
if @type='日'
begin
insert #t
select convert(varchar(10),dateadd(day,number,@starttime),120)
from master..spt_values
where type='P' and number between 0 and datediff(day,@starttime,@endtime)
set @sql=@sql+'isnull((select top 1 Balance from TZ_Money where convert(varchar(10),[Time],120)<=a.d and Balance is not null order by time desc),0.00) 余额,a.d as [time]'
set @sql=@sql+' from #t a left join TZ_Money t on a.d=convert(varchar(10),t.[Time],120) '
end
set @w=''
if @starttime<>'' and @starttime is not null
set @w=' and datediff(d,'''+convert(varchar(10),@starttime,120)+''',[Time])>=0 '
if @endtime<>'' and @starttime is not null
set @w=@w+' and datediff(d,'''+convert(varchar(10),@endtime,120)+''',[Time])<=0 '
if @uid<>'' and @uid is not null
set @w=@w+' Uid='''+@uid+''''
set @g=' group by a.d'
exec(@sql+@w+@g)
end
go
p_test1 '2010-5-01','2010-9-12','年',''
go
p_test1 '2010-5-01','2010-9-12','月',''
go
p_test1 '2010-5-01','2010-9-12','日',''
/*
（所影响的行数为 3 行）
（所影响的行数为 1 行）
收入 支出 余额 time
---------------------------------------- ---------------------------------------- ------------ ----------
8.00 59.00 49.00 2010
（所影响的行数为 1 行）
（所影响的行数为 5 行）
收入 支出 余额 time
---------------------------------------- ---------------------------------------- ------------ ----------
.00 .00 .00 2010-05
4.00 9.00 95.00 2010-06
.00 .00 95.00 2010-07
4.00 50.00 49.00 2010-08
.00 .00 49.00 2010-09
（所影响的行数为 5 行）
警告: 聚合或其他 SET 操作消除了空值。
（所影响的行数为 135 行）
收入 支出 余额 time
---------------------------------------- ---------------------------------------- ------------ ----------
.00 .00 .00 2010-05-01
.00 .00 .00 2010-05-02
.00 .00 .00 2010-05-03
.00 .00 .00 2010-05-04
......
.00 .00 .00 2010-05-28
.00 .00 .00 2010-05-29
.00 .00 .00 2010-05-30
.00 .00 .00 2010-05-31
.00 .00 .00 2010-06-01
.00 .00 .00 2010-06-02
.00 .00 .00 2010-06-03
.00 .00 .00 2010-06-04
.00 .00 .00 2010-06-05
.00 .00 .00 2010-06-06
4.00 9.00 95.00 2010-06-07
.00 .00 95.00 2010-06-08
.00 .00 95.00 2010-06-09
.00 .00 95.00 2010-06-10
.00 .00 95.00 2010-06-11
.00 .00 95.00 2010-06-12
.00 .00 95.00 2010-06-13
.00 .00 95.00 2010-06-14
.00 .00 95.00 2010-06-15
.00 .00 95.00 2010-06-16
.00 .00 95.00 2010-06-17
.00 .00 95.00 2010-06-18
.00 .00 95.00 2010-06-19
.00 .00 95.00 2010-06-20
.00 .00 95.00 2010-06-21
.00 .00 95.00 2010-06-22
.00 .00 95.00 2010-06-23
.00 .00 95.00 2010-06-24
.......
.00 .00 95.00 2010-08-01
.00 .00 95.00 2010-08-02
.00 .00 95.00 2010-08-03
.00 .00 95.00 2010-08-04
.00 .00 95.00 2010-08-05
.00 .00 95.00 2010-08-06
4.00 50.00 49.00 2010-08-07
.00 .00 49.00 2010-08-08
.00 .00 49.00 2010-08-09
.00 .00 49.00 2010-08-10
.....
.00 .00 49.00 2010-09-06
.00 .00 49.00 2010-09-07
.00 .00 49.00 2010-09-08
.00 .00 49.00 2010-09-09
.00 .00 49.00 2010-09-10
.00 .00 49.00 2010-09-11
.00 .00 49.00 2010-09-12
（所影响的行数为 135 行）
警告: 聚合或其他 SET 操作消除了空值。
扫一扫
8181
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
