create table t_xm --项目表
(
xm_id varchar(50)--项目编号
)
insert into t_xm select '01'
insert into t_xm select '02' create table t_bb1_in --收入表
(
xm_id varchar(50), --项目编号
dr decimal(13,2), --当日收入金额
rq datetime --日期
)
insert into t_bb1_in select '01',100,'2009-1-1'
insert into t_bb1_in select '02',200,'2009-1-1' insert into t_bb1_in select '01',300,'2009-1-2'
insert into t_bb1_in select '02',400,'2009-1-2' create table t_bb1_out --支出表
(
xm_id varchar(50), --项目编号
dr decimal(13,2), --当日支出金额
rq datetime --日期
)
insert into t_bb1_out select '01',10,'2009-1-1'
insert into t_bb1_out select '02',20,'2009-1-1' insert into t_bb1_out select '01',30,'2009-1-2'
insert into t_bb1_out select '02',40,'2009-1-2'
go/*
要得到如下的结果集：--如果是rq=2009-1-1,则：项目编号 当日收入金额 累计收入金额 当日支出金额 累计支出金额 日期
01 100 100 10 10 2009-1-1
02 200 200 20 20 2009-1-1--如果是rq=2009-1-2,则：项目编号 当日收入金额 累计收入金额 当日支出金额 累计支出金额 日期
01 300 400 30 40 2009-1-2
02 400 600 40 60 2009-1-2
--如果是rq是2009-1-2以后的日期，而且没有当日金额，则：当日收入。支出为0，累计数截止到有当日金额的那一天项目编号 当日收入金额 累计收入金额 当日支出金额 累计支出金额 日期
01 0 400 0 40 2009-1-3
02 0 600 0 60 2009-1-3
*/
drop table t_xm
drop table t_bb1_in
drop table t_bb1_out
--适用所有日期
create table t_xm --项目表
(
xm_id varchar(50)--项目编号
)
insert into t_xm select '01'
insert into t_xm select '02' create table t_bb1_in --收入表
(
xm_id varchar(50), --项目编号
dr decimal(13,2), --当日收入金额
rq datetime --日期
)
insert into t_bb1_in select '01',100,'2009-1-1'
insert into t_bb1_in select '02',200,'2009-1-1' insert into t_bb1_in select '01',300,'2009-1-2'
insert into t_bb1_in select '02',400,'2009-1-2'
create table t_bb1_out --支出表
(
xm_id varchar(50), --项目编号
dr decimal(13,2), --当日支出金额
rq datetime --日期
)
insert into t_bb1_out select '01',10,'2009-1-1'
insert into t_bb1_out select '02',20,'2009-1-1' insert into t_bb1_out select '01',30,'2009-1-2'
insert into t_bb1_out select '02',40,'2009-1-2' declare @date table (rq datetime )
insert into @date
select '2009-1-3'
select r.xm_id as 项目编号,
isnull((select sum(dr) from t_bb1_in where r.xm_id = xm_id and rq = '2009-1-3'),0) as 当日收入金额,
isnull((select sum(dr) from t_bb1_in where r.xm_id = xm_id and rq <= '2009-1-3'),0) as 累计收入金额,
isnull((select sum(dr) from t_bb1_out where r.xm_id = xm_id and rq = '2009-1-3'),0) as 当日收入金额,
isnull((select sum(dr) from t_bb1_out where r.xm_id = xm_id and rq <= '2009-1-3'),0) as 累计收入金额,
t.rq as 日期
from
(select rq from t_bb1_in
union
select rq from t_bb1_out
union
select rq from @date) t,t_xm r
where t.rq = '2009-1-3'
--结果
---------------------------------------
01 .00 400.00 .00 40.00 2009-01-03 00:00:00.000
02 .00 600.00 .00 60.00 2009-01-03 00:00:00.000
set @rq='2009-01-03'
select
a.xm_id as 项目编号,
isnull((select sum(dr) from t_bb1_in where xm_id=a.xm_id and datediff(dd,rq,@rq)=0),0) as 当日收入金额,
isnull((select sum(dr) from t_bb1_in where xm_id=a.xm_id and rq<=@rq),0) as 累计收入金额,
isnull((select sum(dr) from t_bb1_out where xm_id=a.xm_id and datediff(dd,rq,@rq)=0),0) as 当日收入金额,
isnull((select sum(dr) from t_bb1_out where xm_id=a.xm_id and rq<=@rq),0) as 累计收入金额,
convert(varchar(10),@rq,120) as 日期
from
t_xm a
left join t_bb1_in b on a.xm_id=b.xm_id and datediff(dd,b.rq,@rq)=0
left join t_bb1_out c on c.xm_id=a.xm_id and datediff(dd,c.rq,@rq)=0/**
项目编号 当日收入金额 累计收入金额 当日收入金额 累计收入金额 日期
-------------------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ----------
01 .00 400.00 .00 40.00 2009-01-03
02 .00 600.00 .00 60.00 2009-01-03（所影响的行数为 2 行）
**/
(select sum(dr) from t_bb1_in where rq<=i.rq and xm_id =i.xm_id) ,
(select sum(dr) from t_bb1_out where rq<=i.rq and xm_id =i.xm_id)
from t_bb1_in i
left join t_bb1_out o on o.xm_id = i.xm_id and o.rq =i.rq
where i.rq ='2009-01-02'
group by i.xm_id,i.rq,i.dr,o.dr
create table t_xm( xm_id varchar(50))
insert into t_xm select '01'
insert into t_xm select '02'
create table t_bb1_in( xm_id varchar(50),dr decimal(13,2),rq datetime)
insert into t_bb1_in select '01',100,'2009-1-1'
insert into t_bb1_in select '02',200,'2009-1-1'
insert into t_bb1_in select '01',300,'2009-1-2'
insert into t_bb1_in select '02',400,'2009-1-2'
create table t_bb1_out( xm_id varchar(50),dr decimal(13,2),rq datetime)
insert into t_bb1_out select '01',10,'2009-1-1'
insert into t_bb1_out select '02',20,'2009-1-1'
insert into t_bb1_out select '01',30,'2009-1-2'
insert into t_bb1_out select '02',40,'2009-1-2'
godeclare @dt as datetime
set @dt = '2009-1-1'select t1.xm_id 项目编号,
t2.dr 当日收入金额,
t2.lj 累计收入金额,
t3.dr 当日支出金额,
t3.lj 累计支出金额,
@dt 日期
from t_xm t1 left join
(select xm_id ,
isnull((select dr from t_bb1_in where xm_id = t.xm_id and rq = @dt ),0) dr ,
isnull((select sum(dr) from t_bb1_in where xm_id = t.xm_id and rq <= @dt ),0) lj
from t_bb1_in t
where rq <= @dt
group by xm_id) t2 on t1.xm_id = t2.xm_id left join
(select xm_id ,
isnull((select dr from t_bb1_out where xm_id = t.xm_id and rq = @dt ),0) dr ,
isnull((select sum(dr) from t_bb1_out where xm_id = t.xm_id and rq <= @dt ),0) lj
from t_bb1_out t
where rq <= @dt
group by xm_id) t3 on t1.xm_id = t3.xm_id
order by t1.xm_iddrop table t_xm , t_bb1_in , t_bb1_out/*
项目编号 当日收入金额 累计收入金额 当日支出金额 累计支出金额 日期
-------------------------------------------------- --------------- ---------------------------------------- --------------- ---------------------------------------- ------------------------------------------------------
01 100.00 100.00 10.00 10.00 2009-01-01 00:00:00.000
02 200.00 200.00 20.00 20.00 2009-01-01 00:00:00.000（所影响的行数为 2 行）
*/
--修改下
create table t_xm --项目表
(
xm_id varchar(50)--项目编号
)
insert into t_xm select '01'
insert into t_xm select '02' create table t_bb1_in --收入表
(
xm_id varchar(50), --项目编号
dr decimal(13,2), --当日收入金额
rq datetime --日期
)
insert into t_bb1_in select '01',100,'2009-1-1'
insert into t_bb1_in select '02',200,'2009-1-1' insert into t_bb1_in select '01',300,'2009-1-2'
insert into t_bb1_in select '02',400,'2009-1-2'
create table t_bb1_out --支出表
(
xm_id varchar(50), --项目编号
dr decimal(13,2), --当日支出金额
rq datetime --日期
)
insert into t_bb1_out select '01',10,'2009-1-1'
insert into t_bb1_out select '02',20,'2009-1-1' insert into t_bb1_out select '01',30,'2009-1-2'
insert into t_bb1_out select '02',40,'2009-1-2' declare @date datetimeselect @date = '2009-1-3'
select r.xm_id as 项目编号,
isnull((select sum(dr) from t_bb1_in where r.xm_id = xm_id and rq = @date),0) as 当日收入金额,
isnull((select sum(dr) from t_bb1_in where r.xm_id = xm_id and rq <= @date),0) as 累计收入金额,
isnull((select sum(dr) from t_bb1_out where r.xm_id = xm_id and rq = @date),0) as 当日收入金额,
isnull((select sum(dr) from t_bb1_out where r.xm_id = xm_id and rq <= @date),0) as 累计收入金额,
t.rq as 日期
from
(select rq from t_bb1_in
union
select rq from t_bb1_out
union
select @date as rq) t,t_xm r
where t.rq = @date
(
xm_id varchar(50)--项目编号
)
insert into t_xm select '01'
insert into t_xm select '02' create table t_bb1_in --收入表
(
xm_id varchar(50), --项目编号
dr decimal(13,2), --当日收入金额
rq datetime --日期
)
insert into t_bb1_in select '01',100,'2009-1-1'
insert into t_bb1_in select '02',200,'2009-1-1' insert into t_bb1_in select '01',300,'2009-1-2'
insert into t_bb1_in select '02',400,'2009-1-2'
create table t_bb1_out --支出表
(
xm_id varchar(50), --项目编号
dr decimal(13,2), --当日支出金额
rq datetime --日期
)
insert into t_bb1_out select '01',10,'2009-1-1'
insert into t_bb1_out select '02',20,'2009-1-1' insert into t_bb1_out select '01',30,'2009-1-2'
insert into t_bb1_out select '02',40,'2009-1-2'
go
--select * from t_xm
--select * from t_bb1_in
--select * from t_bb1_out
declare @time datetime
set @time='2009-01-02'
select
a.xm_id,a.rq,a.dr,b.dr,sum(a.dr),sum(b.dr),
(select sum(dr) from t_bb1_in where rq<=a.rq and xm_id =a.xm_id) ,
(select sum(dr) from t_bb1_out where rq<=a.rq and xm_id =a.xm_id)
from
t_bb1_in a
left join t_bb1_out b on
a.xm_id = b.xm_id
and
a.rq =b.rq
where
a.rq =@time
group by
a.xm_id,a.rq,a.dr,b.drdrop table t_xm,t_bb1_in,t_bb1_out
/*xm_id rq dr dr
-------------------------------------------------- ----------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
01 2009-01-02 00:00:00.000 300.00 30.00 300.00 30.00 400.00 40.00
02 2009-01-02 00:00:00.000 400.00 40.00 400.00 40.00 600.00 60.00(2 行受影响)
*/
insert into t_xm select '01'
insert into t_xm select '02'
create table t_bb1_in( xm_id varchar(50),dr decimal(13,2),rq datetime)
insert into t_bb1_in select '01',100,'2009-1-1'
insert into t_bb1_in select '02',200,'2009-1-1'
insert into t_bb1_in select '01',300,'2009-1-2'
insert into t_bb1_in select '02',400,'2009-1-2'
create table t_bb1_out( xm_id varchar(50),dr decimal(13,2),rq datetime)
insert into t_bb1_out select '01',10,'2009-1-1'
insert into t_bb1_out select '02',20,'2009-1-1'
insert into t_bb1_out select '01',30,'2009-1-2'
insert into t_bb1_out select '02',40,'2009-1-2'
godeclare @dt as datetimeset @dt = '2009-1-1'
select 项目编号 = t.xm_id,
当日收入金额 = isnull((select dr from t_bb1_in where xm_id = t.xm_id and rq = @dt),0) ,
累计收入金额 = isnull((select sum(dr) from t_bb1_in where xm_id = t.xm_id and rq <= @dt),0),
当日支出金额 = isnull((select dr from t_bb1_out where xm_id = t.xm_id and rq = @dt),0) ,
累计支出金额 = isnull((select sum(dr) from t_bb1_out where xm_id = t.xm_id and rq <= @dt),0),
日期 = @dt
from t_xm t
order by t.xm_id
/*
项目编号 当日收入金额 累计收入金额 当日支出金额 累计支出金额 日期
-------------------------------------------------- --------------- ---------------------------------------- --------------- ---------------------------------------- ------------------------------------------------------
01 100.00 100.00 10.00 10.00 2009-01-01 00:00:00.000
02 200.00 200.00 20.00 20.00 2009-01-01 00:00:00.000（所影响的行数为 2 行）
*/set @dt = '2009-1-2'
select 项目编号 = t.xm_id,
当日收入金额 = isnull((select dr from t_bb1_in where xm_id = t.xm_id and rq = @dt),0) ,
累计收入金额 = isnull((select sum(dr) from t_bb1_in where xm_id = t.xm_id and rq <= @dt),0),
当日支出金额 = isnull((select dr from t_bb1_out where xm_id = t.xm_id and rq = @dt),0) ,
累计支出金额 = isnull((select sum(dr) from t_bb1_out where xm_id = t.xm_id and rq <= @dt),0),
日期 = @dt
from t_xm t
order by t.xm_id
/*项目编号 当日收入金额 累计收入金额 当日支出金额 累计支出金额 日期
-------------------------------------------------- --------------- ---------------------------------------- --------------- ---------------------------------------- ------------------------------------------------------
01 300.00 400.00 30.00 40.00 2009-01-02 00:00:00.000
02 400.00 600.00 40.00 60.00 2009-01-02 00:00:00.000（所影响的行数为 2 行）
*/set @dt = '2009-1-3'
select 项目编号 = t.xm_id,
当日收入金额 = isnull((select dr from t_bb1_in where xm_id = t.xm_id and rq = @dt),0) ,
累计收入金额 = isnull((select sum(dr) from t_bb1_in where xm_id = t.xm_id and rq <= @dt),0),
当日支出金额 = isnull((select dr from t_bb1_out where xm_id = t.xm_id and rq = @dt),0) ,
累计支出金额 = isnull((select sum(dr) from t_bb1_out where xm_id = t.xm_id and rq <= @dt),0),
日期 = @dt
from t_xm t
order by t.xm_id
/*
项目编号 当日收入金额 累计收入金额 当日支出金额 累计支出金额 日期
-------------------------------------------------- --------------- ---------------------------------------- --------------- ---------------------------------------- ------------------------------------------------------
01 .00 400.00 .00 40.00 2009-01-03 00:00:00.000
02 .00 600.00 .00 60.00 2009-01-03 00:00:00.000（所影响的行数为 2 行）
*/drop table t_xm , t_bb1_in , t_bb1_out
为什么1月1号的累计收入是100，200呢， 不是400，600吗？？create table t_xm --项目表
(
xm_id varchar(50)--项目编号
)
insert into t_xm select '01'
insert into t_xm select '02' create table t_bb1_in --收入表
(
xm_id varchar(50), --项目编号
dr decimal(13,2), --当日收入金额
rq datetime --日期
)
insert into t_bb1_in select '01',100,'2009-1-1'
insert into t_bb1_in select '02',200,'2009-1-1' insert into t_bb1_in select '01',300,'2009-1-2'
insert into t_bb1_in select '02',400,'2009-1-2'
create table t_bb1_out --支出表
(
xm_id varchar(50), --项目编号
dr decimal(13,2), --当日支出金额
rq datetime --日期
)
insert into t_bb1_out select '01',10,'2009-1-1'
insert into t_bb1_out select '02',20,'2009-1-1' insert into t_bb1_out select '01',30,'2009-1-2'
insert into t_bb1_out select '02',40,'2009-1-2'
go
SELECT a.xm_id as 项目编号, SUM(B.dr) AS 当日收入金额,
(SELECT SUM(DR) FROM t_bb1_in WHERE A.XM_ID = XM_ID) AS 累计收入金额,
(SELECT SUM(DR) FROM t_bb1_out WHERE A.XM_ID = XM_ID AND B.RQ=RQ) AS 当日支出金额,
(SELECT SUM(DR) FROM t_bb1_out WHERE A.XM_ID = XM_ID) AS 累计收入金额
, b.rq
FROM t_xm A LEFT JOIN t_bb1_in B ON A.xm_id = B.xm_id
GROUP BY A.XM_ID, B.RQ
order by b.rq, a.xm_id
drop table t_bb1_out
drop table t_bb1_in
drop table t_xm/*
项目编号 当日收入金额 累计收入金额 当日支出金额 累计收入金额 rq
-------------------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ------------------------------------------------------
01 100.00 400.00 10.00 40.00 2009-01-01 00:00:00.000
02 200.00 600.00 20.00 60.00 2009-01-01 00:00:00.000
01 300.00 400.00 30.00 40.00 2009-01-02 00:00:00.000
02 400.00 600.00 40.00 60.00 2009-01-02 00:00:00.000（所影响的行数为 4 行）
create table t_xm --项目表
(
xm_id varchar(50)--项目编号
)
insert into t_xm select '01'
insert into t_xm select '02' create table t_bb1_in --收入表
(
xm_id varchar(50), --项目编号
dr decimal(13,2), --当日收入金额
rq datetime --日期
)
insert into t_bb1_in select '01',100,'2009-1-1'
insert into t_bb1_in select '02',200,'2009-1-1' insert into t_bb1_in select '01',300,'2009-1-2'
insert into t_bb1_in select '02',400,'2009-1-2'
create table t_bb1_out --支出表
(
xm_id varchar(50), --项目编号
dr decimal(13,2), --当日支出金额
rq datetime --日期
)
insert into t_bb1_out select '01',10,'2009-1-1'
insert into t_bb1_out select '02',20,'2009-1-1' insert into t_bb1_out select '01',30,'2009-1-2'
insert into t_bb1_out select '02',40,'2009-1-2'
go declare @date datetime
set @date = '2009-1-1'
SELECT a.xm_id as 项目编号,
isnull((select sum(dr) from t_bb1_in where a.XM_ID = XM_ID and rq=@date),0) AS 当日收入金额,
isnull((SELECT SUM(DR) FROM t_bb1_in WHERE A.XM_ID = XM_ID and rq<=@date),0) AS 累计收入金额,
isnull((SELECT SUM(DR) FROM t_bb1_out WHERE A.XM_ID = XM_ID AND rq=@date),0) AS 当日支出金额,
isnull((SELECT SUM(DR) FROM t_bb1_out WHERE A.XM_ID = XM_ID and rq<=@date),0) AS 累计收入金额
, @date
FROM t_xm A
GROUP BY A.XM_ID
order by a.xm_id
/*
项目编号 当日收入金额 累计收入金额 当日支出金额 累计收入金额 rq
-------------------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ------------------------------------------------------
01 100.00 100.00 10.00 10.00 2009-01-01 00:00:00.000
02 200.00 200.00 20.00 20.00 2009-01-01 00:00:00.000（所影响的行数为 2 行）
*/
set @date = '2009-1-2'
SELECT a.xm_id as 项目编号,
isnull((select sum(dr) from t_bb1_in where a.XM_ID = XM_ID and rq=@date),0) AS 当日收入金额,
isnull((SELECT SUM(DR) FROM t_bb1_in WHERE A.XM_ID = XM_ID and rq<=@date),0) AS 累计收入金额,
isnull((SELECT SUM(DR) FROM t_bb1_out WHERE A.XM_ID = XM_ID AND rq=@date),0) AS 当日支出金额,
isnull((SELECT SUM(DR) FROM t_bb1_out WHERE A.XM_ID = XM_ID and rq<=@date),0) AS 累计收入金额
, @date
FROM t_xm A
GROUP BY A.XM_ID
order by a.xm_id
/*
项目编号 当日收入金额 累计收入金额 当日支出金额 累计收入金额 rq
-------------------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ------------------------------------------------------
01 300.00 400.00 30.00 40.00 2009-01-02 00:00:00.000
02 400.00 600.00 40.00 60.00 2009-01-02 00:00:00.000（所影响的行数为 2 行）
*/
set @date = '2009-1-3'
SELECT a.xm_id as 项目编号,
isnull((select sum(dr) from t_bb1_in where a.XM_ID = XM_ID and rq=@date),0) AS 当日收入金额,
isnull((SELECT SUM(DR) FROM t_bb1_in WHERE A.XM_ID = XM_ID and rq<=@date),0) AS 累计收入金额,
isnull((SELECT SUM(DR) FROM t_bb1_out WHERE A.XM_ID = XM_ID AND rq=@date),0) AS 当日支出金额,
isnull((SELECT SUM(DR) FROM t_bb1_out WHERE A.XM_ID = XM_ID and rq<=@date),0) AS 累计收入金额
, @date
FROM t_xm A
GROUP BY A.XM_ID
order by a.xm_id/*
项目编号 当日收入金额 累计收入金额 当日支出金额 累计收入金额
-------------------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ------------------------------------------------------
01 .00 400.00 .00 40.00 2009-01-03 00:00:00.000
02 .00 600.00 .00 60.00 2009-01-03 00:00:00.000（所影响的行数为 2 行）
*/drop table t_bb1_out
drop table t_bb1_in
drop table t_xm
(
xm_id varchar(50)--项目编号
)
insert into t_xm select '01'
insert into t_xm select '02' create table t_bb1_in --收入表
(
xm_id varchar(50), --项目编号
dr decimal(13,2), --当日收入金额
rq datetime --日期
)
insert into t_bb1_in select '01',100,'2009-1-1'
insert into t_bb1_in select '02',200,'2009-1-1' insert into t_bb1_in select '01',300,'2009-1-2'
insert into t_bb1_in select '02',400,'2009-1-2'
create table t_bb1_out --支出表
(
xm_id varchar(50), --项目编号
dr decimal(13,2), --当日支出金额
rq datetime --日期
)
insert into t_bb1_out select '01',10,'2009-1-1'
insert into t_bb1_out select '02',20,'2009-1-1' insert into t_bb1_out select '01',30,'2009-1-2'
insert into t_bb1_out select '02',40,'2009-1-2'
go ---------------------------------------------------.>
if object_id('pp')is not null drop proc pp
go
create proc pp
@rq datetime
as select
项目编号=b.xm_id,
当日收入金额=sum(case when b.rq=@rq then b.dr else 0 end),
累计收入金额=sum(case when datediff(day,b.rq,@rq)>=0 then b.dr else 0 end),
当日支出金额=sum(case when c.rq=@rq then c.dr else 0 end),
累计支出金额=sum(case when datediff(day,c.rq,@rq)>=0 then c.dr else 0 end),
日期=@rq
from t_bb1_in b,t_bb1_out c
where b.xm_id=c.xm_id and b.rq=c.rq
group by b.xm_id
go exec pp '2009-1-1'/*
项目编号 当日收入金额 累计收入金额 当日支出金额 累计支出金额 日期
01 100 100 10 10 2009-1-1
02 200 200 20 20 2009-1-1 */exec pp '2009-1-2'
/*
项目编号 当日收入金额 累计收入金额 当日支出金额 累计支出金额 日期
-------------------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- -----------------------
01 300.00 400.00 30.00 40.00 2009-01-02 00:00:00.000
02 400.00 600.00 40.00 60.00 2009-01-02 00:00:00.000
*/exec pp '2009-1-3'
/*
项目编号 当日收入金额 累计收入金额 当日支出金额 累计支出金额 日期
-------------------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- -----------------------
01 0.00 400.00 0.00 40.00 2009-01-03 00:00:00.000
02 0.00 600.00 0.00 60.00 2009-01-03 00:00:00.000
*/drop table t_xm
drop table t_bb1_in
drop table t_bb1_out