sql语句如下：
select convert(datetime,'2001-2-3') as '日期',project as '工程',0 as '收支' ,Sum(Inout) as '结存'
from Test_JC
Where Dt < '2001-02-03'
group by project
union all
Select DT as '日期',Project as '工程',Sum(InOut) as '收支',
(select Sum(InOut) from Test_Jc where Dt <= a.Dt and project = a.project) as '结存'
From Test_JC a
where Dt > '2001-02-03' and Dt <= '2001-03-03'
Group by DT,Project
Order by '工程','日期'
运行结果如下：
日期 工程 收支 结存
--------------------------- ---------- ---------------------------------------- ----------------------------------------
2001-02-03 00:00:00.000 a 0 6
2001-02-04 00:00:00.000 a 3 9
2001-03-01 00:00:00.000 a 2 11
2001-03-02 00:00:00.000 a 3 14
2001-02-03 00:00:00.000 b 0 2
2001-02-05 00:00:00.000 b 1 3
2001-03-02 00:00:00.000 b -1 2
表：
if exists (select * from sysobjects where id = object_id(N'[dbo].[Test_JC]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Test_JC]
GO
CREATE TABLE [dbo].[Test_JC] (
[Dt] [smalldatetime] NULL ,
[Project] [varchar] (10) NULL ,
[InOut] [numeric](18, 0) NULL
) ON [PRIMARY]
GO
数据如下：
日期 项目 收支
2001-1-1 a 2
2001-1-1 b 1
2001-2-1 a 1
2001-2-1 a 3
2001-2-2 b 1
2001-2-4 a 3
2001-2-5 b 1
2001-3-1 a -2
2001-3-1 a 4
2001-3-2 b -1
2001-3-2 a 3
