--目前实现按要求删除数据或更改为0
create table 实收表(bh int, skrq datetime, ssje int )
insert into 实收表 values(1 ,'2008-5-2', 6 )
insert into 实收表 values(1 ,'2008-6-2', 12) --删除
insert into 实收表 values(1 ,'2008-7-2', 9 )
insert into 实收表 values(1 ,'2008-8-2', 11)
create table 还款表(bh int ,xlrq datetime, skrq datetime, hkje int)
insert into 还款表 values(1 ,'2008-1-1' ,'2008-5-2' ,6)
insert into 还款表 values(1 ,'2008-1-1' ,'2008-6-2' ,2) --删除
insert into 还款表 values(1 ,'2008-2-1' ,'2008-6-2' ,8) --删除
insert into 还款表 values(1 ,'2008-3-1' ,'2008-6-2' ,2) --删除
insert into 还款表 values(1 ,'2008-3-1' ,'2008-7-2' ,6)
goselect m.bh , m.xlrq , m.skrq ,
hkje = case when (select sum(hkje) from 还款表 where bh = m.bh and skrq >= n.skrq and skrq <= m.skrq) < n.ssje then 0
when (select sum(hkje) from 还款表 where bh = m.bh and skrq >= n.skrq and skrq <= m.skrq) >= n.ssje and
isnull((select sum(hkje) from 还款表 where bh = m.bh and skrq >= n.skrq and skrq < m.skrq),0) < n.ssje then
(select sum(hkje) from 还款表 where bh = m.bh and skrq >= n.skrq and skrq <= m.skrq) - n.ssje
else m.hkje
end
from 还款表 m , 实收表 n
where m.bh = n.bh and n.skrq = '2008-6-2' drop table 实收表,还款表/*
bh xlrq skrq hkje
----------- ------------------------------------------------------ ------------------------------------------------------ -----------
1 2008-01-01 00:00:00.000 2008-05-02 00:00:00.000 6
1 2008-01-01 00:00:00.000 2008-06-02 00:00:00.000 0
1 2008-02-01 00:00:00.000 2008-06-02 00:00:00.000 0
1 2008-03-01 00:00:00.000 2008-06-02 00:00:00.000 0
1 2008-03-01 00:00:00.000 2008-07-02 00:00:00.000 6(所影响的行数为 5 行)
*/
create table 实收表(bh int, skrq datetime, ssje int )
insert into 实收表 values(1 ,'2008-5-2', 6 )
insert into 实收表 values(1 ,'2008-6-2', 12) --删除
insert into 实收表 values(1 ,'2008-7-2', 9 )
insert into 实收表 values(1 ,'2008-8-2', 11)
create table 还款表(bh int ,xlrq datetime, skrq datetime, hkje int)
insert into 还款表 values(1 ,'2008-1-1' ,'2008-5-2' ,6)
insert into 还款表 values(1 ,'2008-1-1' ,'2008-6-2' ,2) --删除
insert into 还款表 values(1 ,'2008-2-1' ,'2008-6-2' ,8) --删除
insert into 还款表 values(1 ,'2008-3-1' ,'2008-6-2' ,2) --删除
insert into 还款表 values(1 ,'2008-3-1' ,'2008-7-2' ,6)
goselect m.bh , m.xlrq , m.skrq ,
hkje = case when (select sum(hkje) from 还款表 where bh = m.bh and skrq >= n.skrq and skrq <= m.skrq) < n.ssje then 0
when (select sum(hkje) from 还款表 where bh = m.bh and skrq >= n.skrq and skrq <= m.skrq) >= n.ssje and
isnull((select sum(hkje) from 还款表 where bh = m.bh and skrq >= n.skrq and skrq < m.skrq),0) < n.ssje then
(select sum(hkje) from 还款表 where bh = m.bh and skrq >= n.skrq and skrq <= m.skrq) - n.ssje
else m.hkje
end
from 还款表 m , 实收表 n
where m.bh = n.bh and n.skrq = '2008-6-2' drop table 实收表,还款表/*
bh xlrq skrq hkje
----------- ------------------------------------------------------ ------------------------------------------------------ -----------
1 2008-01-01 00:00:00.000 2008-05-02 00:00:00.000 6
1 2008-01-01 00:00:00.000 2008-06-02 00:00:00.000 0
1 2008-02-01 00:00:00.000 2008-06-02 00:00:00.000 0
1 2008-03-01 00:00:00.000 2008-06-02 00:00:00.000 0
1 2008-03-01 00:00:00.000 2008-07-02 00:00:00.000 6(所影响的行数为 5 行)
*/
http://topic.csdn.net/u/20080417/23/f28b25a5-7b05-47ae-8e76-a3ddb69c4876.html?seed=1316830002
已经做出,就不做了.
http://topic.csdn.net/u/20080417/23/f28b25a5-7b05-47ae-8e76-a3ddb69c4876.html
create table xlb(bh int,xlrq datetime,xlje int)
create table ssb(bh int,skrq datetime,ssje int)
go
create view hkzb
as
select bh,convert(char(10),xlrq,120) xlrq,convert(char(10),skrq,120) skrq,hkje from(
select a.bh,xlrq,skrq,hkje=case when (zxlje-xlje)<=(zssje-ssje)
then case when zxlje<=zssje then zxlje-(zssje-ssje) else ssje end
else case when zxlje<=zssje then xlje else zssje-(zxlje-xlje) end end from
(select bh,xlrq,xlje,zxlje=(select sum(xlje) from xlb where bh = a.bh and xlrq<=a.xlrq) from xlb a)a,
(select bh,skrq,ssje,zssje=(select sum(ssje) from ssb where bh = a.bh and skrq<=a.skrq) from ssb a)b
where a.bh = b.bh and ((zxlje>zssje and zxlje-zssje <xlje) or (zssje>=zxlje and zssje-zxlje <ssje)))c
go
--测试 初始化数据
set nocount on
insert xlb select 1,'2008-1-1',8
insert xlb select 1,'2008-2-1',8
insert xlb select 1,'2008-3-1',8
insert xlb select 2,'2008-1-1',8
insert xlb select 2,'2008-2-1',8
insert xlb select 2,'2008-3-1',8
insert ssb select 1,'2008-5-2',6
insert ssb select 1,'2008-6-2',12
insert ssb select 1,'2008-7-2',9
insert ssb select 1,'2008-8-2',11
insert ssb select 2,'2008-5-2',6
insert ssb select 2,'2008-6-2',12select * from hkzb order by bh,xlrq,skrq
/*
bh xlrq skrq hkje
----------- ---------- ---------- -----------
1 2008-01-01 2008-05-02 6
1 2008-01-01 2008-06-02 2
1 2008-02-01 2008-06-02 8
1 2008-03-01 2008-06-02 2
1 2008-03-01 2008-07-02 6
2 2008-01-01 2008-05-02 6
2 2008-01-01 2008-06-02 2
2 2008-02-01 2008-06-02 8
2 2008-03-01 2008-06-02 2
*/
delete from ssb where bh=1 and skrq='2008-6-2' --删除
select * from hkzb order by bh,xlrq,skrq
/*
bh xlrq skrq hkje
----------- ---------- ---------- -----------
1 2008-01-01 2008-05-02 6
1 2008-01-01 2008-07-02 2
1 2008-02-01 2008-07-02 7
1 2008-02-01 2008-08-02 1
1 2008-03-01 2008-08-02 8
2 2008-01-01 2008-05-02 6
2 2008-01-01 2008-06-02 2
2 2008-02-01 2008-06-02 8
2 2008-03-01 2008-06-02 2
*/
go
drop view hkzb
go
drop table xlb,ssb
set nocount on
create table xlb(bh int,xlrq datetime,xlje int)
create table ssb(bh int,skrq datetime,ssje int)
create table hkb(bh int,xlrq datetime,skrq datetime,hkje int)
go
create trigger t_xlb on ssb for insert,delete,update
as
begin
--删除下量表中需要删除的,这是收款表的,如果是下量表触发器将a.skrq = b.skrq 改成 a.xlrq = b.xlrq后面的都一样。
delete a from hkb a,deleted b where a.bh = b.bh and a.skrq = b.skrq--重新计算
--1、生成需要变更的客户
select distinct bh into #bh from (select bh from inserted union select bh from deleted) a--2、生成需要重新计算的xlb
select a.bh,a.xlrq,xlje-isnull(hkje,0) xlje into #xlb from
(select a.bh,xlrq,xlje from xlb a,#bh b where a.bh = b.bh) a left join
(select a.bh,xlrq,sum(hkje) hkje from hkb a,#bh b where a.bh = b.bh group by a.bh,xlrq) b
on a.bh = b.bh and a.xlrq = b.xlrq where xlje-isnull(hkje,0)>0
--3、生成需要重新计算的ssb
select a.bh,a.skrq,ssje-isnull(hkje,0) ssje into #ssb from
(select a.bh,skrq,ssje from ssb a,#bh b where a.bh = b.bh) a left join
(select a.bh,skrq,sum(hkje) hkje from hkb a,#bh b where a.bh = b.bh group by a.bh,skrq) b
on a.bh = b.bh and a.skrq = b.skrq where ssje-isnull(hkje,0)>0
--按照还款规则插入还款表
insert hkb select bh,xlrq,skrq,hkje from( select a.bh,xlrq,skrq,hkje=case when (zxlje-xlje)<=(zssje-ssje)
then case when zxlje<=zssje then zxlje-(zssje-ssje) else ssje end
else case when zxlje<=zssje then xlje else zssje-(zxlje-xlje) end end from
(select bh,xlrq,xlje,zxlje=(select sum(xlje) from #xlb where bh = a.bh and xlrq<=a.xlrq) from #xlb a)a,
(select bh,skrq,ssje,zssje=(select sum(ssje) from #ssb where bh = a.bh and skrq<=a.skrq) from #ssb a)b
where a.bh = b.bh and ((zxlje>zssje and zxlje-zssje <xlje) or (zssje>=zxlje and zssje-zxlje <ssje)))c
end
go
--测试数据,开始HKB为空,插入下面7条数据
insert xlb select 1,'2008-1-1',8
insert xlb select 1,'2008-2-1',8
insert xlb select 1,'2008-3-1',8
insert ssb select 1,'2008-5-2',6
insert ssb select 1,'2008-6-2',12
insert ssb select 1,'2008-7-2',9
insert ssb select 1,'2008-8-2',11
select bh,convert(char(10),xlrq,120) xlrq,convert(char(10),skrq,120) skrq,hkje from hkb order by bh,xlrq,skrq
/*bh xlrq skrq hkje
----------- ---------- ---------- -----------
1 2008-01-01 2008-05-02 6
1 2008-01-01 2008-06-02 2
1 2008-02-01 2008-06-02 8
1 2008-03-01 2008-06-02 2
1 2008-03-01 2008-07-02 6*/
delete ssb where skrq = '2008-06-2'
select bh,convert(char(10),xlrq,120) xlrq,convert(char(10),skrq,120) skrq,hkje from hkb order by bh,xlrq,skrq
/*bh xlrq skrq hkje
----------- ---------- ---------- -----------
1 2008-01-01 2008-05-02 6
1 2008-01-01 2008-07-02 2
1 2008-02-01 2008-07-02 1
1 2008-02-01 2008-08-02 7
1 2008-03-01 2008-07-02 6
1 2008-03-01 2008-08-02 2*/
go
drop table xlb,ssb,hkb
set nocount on
create table xlb(bh int,xlrq datetime,xlje int)
create table ssb(bh int,skrq datetime,ssje int)
create table hkb(bh int,xlrq datetime,skrq datetime,hkje int)
go
--下量表触发器
create trigger t_xlb on xlb for insert,delete,update
as
begin
--删除还款表中需要删除的。
delete a from hkb a,deleted b where a.bh = b.bh and a.xlrq = b.xlrq
--重新计算
--1、生成需要变更的客户
select distinct bh into #bh from (select bh from inserted union select bh from deleted) a--2、生成需要重新计算的xlb
select a.bh,a.xlrq,xlje-isnull(hkje,0) xlje into #xlb from
(select a.bh,xlrq,xlje from xlb a,#bh b where a.bh = b.bh) a left join
(select a.bh,xlrq,sum(hkje) hkje from hkb a,#bh b where a.bh = b.bh group by a.bh,xlrq) b
on a.bh = b.bh and a.xlrq = b.xlrq where xlje-isnull(hkje,0)>0
--3、生成需要重新计算的ssb
select a.bh,a.skrq,ssje-isnull(hkje,0) ssje into #ssb from
(select a.bh,skrq,ssje from ssb a,#bh b where a.bh = b.bh) a left join
(select a.bh,skrq,sum(hkje) hkje from hkb a,#bh b where a.bh = b.bh group by a.bh,skrq) b
on a.bh = b.bh and a.skrq = b.skrq where ssje-isnull(hkje,0)>0
--按照还款规则插入还款表
insert hkb select bh,xlrq,skrq,hkje from( select a.bh,xlrq,skrq,hkje=case when (zxlje-xlje)<=(zssje-ssje)
then case when zxlje<=zssje then zxlje-(zssje-ssje) else ssje end
else case when zxlje<=zssje then xlje else zssje-(zxlje-xlje) end end from
(select bh,xlrq,xlje,zxlje=(select sum(xlje) from #xlb where bh = a.bh and xlrq<=a.xlrq) from #xlb a)a,
(select bh,skrq,ssje,zssje=(select sum(ssje) from #ssb where bh = a.bh and skrq<=a.skrq) from #ssb a)b
where a.bh = b.bh and ((zxlje>zssje and zxlje-zssje <xlje) or (zssje>=zxlje and zssje-zxlje <ssje)))c
end
go
--收款表触发器
create trigger t_ssb on ssb for insert,delete,update
as
begin
--删除还款表中需要删除的。
delete a from hkb a,deleted b where a.bh = b.bh and a.skrq = b.skrq
--重新计算
--1、生成需要变更的客户
select distinct bh into #bh from (select bh from inserted union select bh from deleted) a--2、生成需要重新计算的xlb
select a.bh,a.xlrq,xlje-isnull(hkje,0) xlje into #xlb from
(select a.bh,xlrq,xlje from xlb a,#bh b where a.bh = b.bh) a left join
(select a.bh,xlrq,sum(hkje) hkje from hkb a,#bh b where a.bh = b.bh group by a.bh,xlrq) b
on a.bh = b.bh and a.xlrq = b.xlrq where xlje-isnull(hkje,0)>0
--3、生成需要重新计算的ssb
select a.bh,a.skrq,ssje-isnull(hkje,0) ssje into #ssb from
(select a.bh,skrq,ssje from ssb a,#bh b where a.bh = b.bh) a left join
(select a.bh,skrq,sum(hkje) hkje from hkb a,#bh b where a.bh = b.bh group by a.bh,skrq) b
on a.bh = b.bh and a.skrq = b.skrq where ssje-isnull(hkje,0)>0
--按照还款规则插入还款表
insert hkb select bh,xlrq,skrq,hkje from( select a.bh,xlrq,skrq,hkje=case when (zxlje-xlje)<=(zssje-ssje)
then case when zxlje<=zssje then zxlje-(zssje-ssje) else ssje end
else case when zxlje<=zssje then xlje else zssje-(zxlje-xlje) end end from
(select bh,xlrq,xlje,zxlje=(select sum(xlje) from #xlb where bh = a.bh and xlrq<=a.xlrq) from #xlb a)a,
(select bh,skrq,ssje,zssje=(select sum(ssje) from #ssb where bh = a.bh and skrq<=a.skrq) from #ssb a)b
where a.bh = b.bh and ((zxlje>zssje and zxlje-zssje <xlje) or (zssje>=zxlje and zssje-zxlje <ssje)))c
end
go
--测试数据,开始HKB为空,插入下面7条数据
insert xlb select 1,'2008-1-1',8
insert xlb select 1,'2008-2-1',8
insert xlb select 1,'2008-3-1',8
insert ssb select 1,'2008-5-2',6
insert ssb select 1,'2008-6-2',12
insert ssb select 1,'2008-7-2',9
insert ssb select 1,'2008-8-2',11
select bh,convert(char(10),xlrq,120) xlrq,convert(char(10),skrq,120) skrq,hkje from hkb order by bh,xlrq,skrq
/*bh xlrq skrq hkje
----------- ---------- ---------- -----------
1 2008-01-01 2008-05-02 6
1 2008-01-01 2008-06-02 2 --在下面将被删除
1 2008-02-01 2008-06-02 8 --在下面将被删除
1 2008-03-01 2008-06-02 2 --在下面将被删除
1 2008-03-01 2008-07-02 6*/
delete ssb where skrq = '2008-06-2' --删除一条记录
select bh,convert(char(10),xlrq,120) xlrq,convert(char(10),skrq,120) skrq,hkje from hkb order by bh,xlrq,skrq
/*bh xlrq skrq hkje
----------- ---------- ---------- -----------
1 2008-01-01 2008-05-02 6 --保留:尊重历史存在
1 2008-01-01 2008-07-02 2 --新增:用后面的钱补上
1 2008-02-01 2008-07-02 1 --新增:用后面的钱补上
1 2008-02-01 2008-08-02 7 --新增:用后面的钱补上
1 2008-03-01 2008-07-02 6 --保留:尊重历史存在
1 2008-03-01 2008-08-02 2 --新增:用后面的钱补上
*/
go
drop table xlb,ssb,hkb