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 not null --最后时间
)
go求个存储过程。PROC没写好。构思了下
参数一:@starttime 开始时间 （针对Time字段)
参数二:@endtime 结束时间 （针对Time字段)
参数三:@type (可选值 年、月、日).
参数四:@uid 用户ID
返回数据
收入(Income) 支出(Expenditure) 余额(Balance)
围绕@type处理.
@type = 年
就根据@starttime和@endtime返回一条记录既可,收入和支出都是累加掉。余额就最后一跳记录的余额。因为数据存到表里时，
已经处理掉。最后一条就是用户的余额、。
@type = 月
根据@starttime和endtime查询出这2个时间段内的所有月份的记录。。每月记录累加。没有的也要返回0 0 余额就starttime之前的月的最后一天的余额..
比方说数据如下：id uid income expenditure balance time
1 abc1 1.00 5.00 96 2010-06-7 12:12:12
2 abc1 3.00 4.00 95 2010-06-7 21:12:45
3 abc1 4.00 50.00 49 2010-08-7 09:45:59
如果传入的starttime 2010-5 @endtime 2010-9
数据应该是
income expenditure balance time
0 0 0 2010-05
4 9 95 2010-06
0 0 95 2010-07
4 50 49 2010-08
0 0 49 2010-09
@type = 日
比方说数据如下：id uid income expenditure balance time
1 abc1 1.00 5.00 96 2010-06-7 12:12:12
2 abc1 3.00 4.00 95 2010-06-8 21:12:45
3 abc1 4.00 50.00 49 2010-08-7 09:45:59
如果传入的starttime 2010-5-01 @endtime 2010-9-12
得到的数据应该是
income expedniture balance time
0 0 0 2010-05-01
0 0 0 2010-05-02
0 0 0 2010-05-03
........................................
1.00 5.00 96 2010-06-07
3.00 4.00 95 2010-06-08
0 0 95 2010-06-09
0 0 95 2010-06-10
......................95.................
......................95.................
......................95.................
4.00 50.00 49 2010-08-07
0 0 49 2010-08-08
......................49 ...........
......................49 ...........
......................49 ...........
......................49 ...........
0 0 49 2010-09-12
高手帮忙写个存储过程吧。
---
我想应该是本月的最后一天吧
如果查询的有天是2010-6-8.表里没有这一天的数据。
余额就去2010-6-7 最后一条的余额就可以。
@type=月的话 如starttime=2009-10 endtime=2010-06
数据就是
2009-10
2009-11
2009-12
2010-01
一直到
2010-06
@type=日 也是跟月一样。就是要细化到天了。
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'goif 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'
print @sql
print @w
print @g
exec(@sql+@w+@g)
end
go
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'goif 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
gop_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 操作消除了空值。*/
补充一点：
假如starttime、endtime之间年、月或日的间隔>255(MS 2000)
假如starttime、endtime之间年、月或日的间隔>1024(MS 2005)
需要自己构建序号表，类似master..spt_values
一点小问题。 if @uid<>'' and @uid is not null
set @w=@w+' Uid='''+@uid+''''
这里要加个 and还有传@type的时候还有小问题.
传的如果是2009 和 2010 查出的数据出错了。
如果是像你测试的2009-10-1 2010-11-11就没问题。
不过这个我在传入的时候处理下时间就OK了。太谢谢了。结贴。