22,209
社区成员
发帖
与我相关
我的任务
分享
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([日期] [datetime],[收入] [int],[支出] [int])
INSERT INTO [tb]
SELECT '2000/03/01','50','20' UNION ALL
SELECT '2000/03/02','30','20' UNION ALL
SELECT '2000/03/05','40','50'
select dateadd(dd,number,(select min(日期) from tb)) as 日期,
isnull(m.收入,0) 收入,
isnull(m.支出,0) 支出,
余额=sum(isnull(n.收入,0)-isnull(n.支出,0))
from master..spt_values left join tb m on dateadd(dd,number,(select min(日期) from tb))=m.日期
left join tb n on dateadd(dd,number,(select min(日期) from tb))>=n.日期
where type='P' and number<=(select datediff(dd,min(日期),max(日期)) from tb)
group by m.日期,m.收入,m.支出,number
order by m.日期
/*
日期 收入 支出 余额
------------------------------------------------------ ----------- ----------- -----------
2000-03-01 00:00:00.000 50 20 30
2000-03-02 00:00:00.000 30 20 40
2000-03-03 00:00:00.000 0 0 40
2000-03-04 00:00:00.000 0 0 40
2000-03-05 00:00:00.000 40 50 30
(所影响的行数为 5 行)
*/
--原贴地址http://topic.csdn.net/u/20090510/13/e48161d0-f100-4151-8cfc-0895869706bb.html?seed=1668282403
create table #BUY_IN
(
[date] datetime,
[money] int,
)
insert into #BUY_IN select '2009-1-1',1000
insert into #BUY_IN select '2009-2-1',2000
insert into #BUY_IN select '2009-5-1',500
--说下思路吧!先根据查询的日期,得到年月信息到一个临时表
declare @StartTime datetime
declare @EndTime datetime
set @StartTime='2009-1-1'
set @EndTime='2009-5-10'
declare @i int
set @i=0
create table #11
(
b int,
a int
)
while (dateadd(month,@i,@StartTime)<=@EndTime)
begin
insert into #11 select year(@StartTime),month(@StartTime)+@i
set @i=@i+1
end
select * from #11
select t.b '年',isnull(t.a,0) '月份',isnull(sum(B.[money]),0)'总金额' from #BUY_IN B right join #11 t
on t.a=month(B.[date]) group by t.a,t.b
年 月份 总金额
----------- ----------- -----------
2009 1 1000
2009 2 2000
2009 3 0
2009 4 0
2009 5 500
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/ws_hgo/archive/2009/05/10/4165472.aspx
if not object_id('tb') is null
drop table tb
Go
Create table tb([日期] Datetime,[收入] int,[支出] int)
Insert tb
select '2000/03/01',50,20 union all
select '2000/03/02',30,20 union all
select '2000/03/05',40,50
Go
;with sql_sf
as(
select dateadd(day,number,(select min([日期] )from tb))[日期]
from master..spt_values
where type='P'
and dateadd(day,number,(select min([日期] )from tb))<=(select max([日期] )from tb))
select a.[日期],
isnull(b.收入,0)收入,
isnull(b.支出,0)支出
into #
from sql_sf a left join
tb b on a.[日期]=b.[日期]
select *,
(select sum(收入-支出)from # where 日期<=t.日期)
from # t
drop table #
/*
日期 收入 支出
----------------------- ----------- ----------- -----------
2000-03-01 00:00:00.000 50 20 30
2000-03-02 00:00:00.000 30 20 40
2000-03-03 00:00:00.000 0 0 40
2000-03-04 00:00:00.000 0 0 40
2000-03-05 00:00:00.000 40 50 30
(5 個資料列受到影響)
*/
----------------------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-05-13 11:04:49
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog : http://blog.csdn.net/htl258
----------------------------------------------------------------------------------
--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([日期] [datetime],[收入] [int],[支出] [int])
INSERT INTO [tb]
SELECT '2000/03/01','50','20' UNION ALL
SELECT '2000/03/02','30','20' UNION ALL
SELECT '2000/03/05','40','50'
--SELECT * FROM [tb]
-->SQL查询如下:
;with t as
(
select convert(varchar,dateadd(dd,number,(select min(日期) from tb)),111) 日期,
isnull(b.收入,0) 收入,isnull(b.支出,0) 支出,
isnull(收入,0)-isnull(支出,0) ye
from master..spt_values a
left join tb b
on dateadd(dd,number,(select min(日期) from tb))=b.日期
where a.type='p' and number<=(select datediff(dd,min([日期]),max([日期])) from tb)
)
select 日期,收入,支出,余额=(select sum(ye) from t where 日期<=a.日期)
from t a
/*
日期 收入 支出 余额
------------------------------ ----------- ----------- -----------
2000/03/01 50 20 30
2000/03/02 30 20 40
2000/03/03 0 0 40
2000/03/04 0 0 40
2000/03/05 40 50 30
(5 行受影响)
*/