一个比较复杂的的存储过程

[mailto520]

有三个表：
inTable:
id planId amount
1 09010101 25
2 09010203 30
3 08120501 10
4 08090601 11

OutTable:
id PlanId amount
1 09010101 3
2 09010101 22
3 09010203 30
4 08120501 5
5 08120501 1

PlanTable:
id PlanId plannum PlanDate
1 09010101 25 2008-12-31
2 09010203 30 2009-01-02
3 08120501 10 2008-12-4
4 08090601 11 2008-09-01


说明：
删除PlanTable.PlanDate小于'2009-01-01'，PlanTable.PlanNum<=OutTable.sum(Amount),连接列为PlanID

删除后的结果：
inTable:
id planId amount
2 09010203 30
3 08120501 10
4 08090601 11

OutTable:
id PlanId amount
3 09010203 30
4 08120501 5
5 08120501 1

PlanTable:
id PlanId plannum PlanDate
2 09010203 30 2009-01-02
3 08120501 10 2008-12-4
4 08090601 11 2008-09-01

[ChinaJiaBing]

if OBJECT_ID('outtable') is not null
   drop table outtable
if OBJECT_ID('plantable') is not null
    drop table plantable 
 go
 create table outtable (id int,planid int,amount int)
 insert into outtable select 1,09010101,3
           union all  select 2,09010101,22
           union all  select 3,09010203,30
           union all  select 4,08120501,5
           union all  select 5,08120501,1
create table plantable (id int,planid int,plannum int,plandate datetime)
insert into plantable select 1,09010101,25,'2008-12-31'
           union all  select 2,09010203,30,'2009-01-02'
           union all  select 3,08120501,10,'2008-12-04'
           union all  select 3,08090601,11,'2008-09-01'
-- select b.* from 
--(select planid,sum(amount) amount from outtable
--group by planid) a  where not exists 
--(select * from plantable where plandate>='2008-09-01')
--b on a.planid=b.planid and a.amount=b.plannum
select a.* from
(select * from plantable where plandate>='2008-09-01') a 
where not exists (select 1 from (select planid,sum(amount) amount from outtable
group by planid) b where a.planid=b.planid and b.amount >=a.plannum)

[mailto520]

高手在哪里?

[dawugui]

create table inTable(id int, planId varchar(10) , amount int)
insert into inTable values(1 , '09010101' , 25) 
insert into inTable values(2 , '09010203' , 30) 
insert into inTable values(3 , '08120501' , 10) 
insert into inTable values(4 , '08090601' , 11) 
create table OutTable(id int, PlanId varchar(10), amount int)
insert into OutTable values(1 , '09010101' , 3 )
insert into OutTable values(2 , '09010101' , 22) 
insert into OutTable values(3 , '09010203' , 30) 
insert into OutTable values(4 , '08120501' , 5 )
insert into OutTable values(5 , '08120501' , 1 )
create table PlanTable(id int, PlanId varchar(10), plannum int, PlanDate datetime)
insert into PlanTable values(1 , '09010101' , 25 , '2008-12-31') 
insert into PlanTable values(2 , '09010203' , 30 , '2009-01-02') 
insert into PlanTable values(3 , '08120501' , 10 , '2008-12-4') 
insert into PlanTable values(4 , '08090601' , 11 , '2008-09-01')
go
create table tb(PlanId varchar(10))
go

insert into tb select planid from PlanTable where PlanDate < '2009-01-01' and plannum <= (select sum(amount) from OutTable where PlanId = PlanTable.PlanId)

delete from inTable where planid in (select planid from tb)
delete from OutTable where planid in (select planid from tb)
delete from PlanTable where planid in (select planid from tb)

select * from inTable
/*
id          planId     amount      
----------- ---------- ----------- 
2           09010203   30
3           08120501   10
4           08090601   11

（所影响的行数为 3 行）
*/

select * from OutTable
/*
id          PlanId     amount      
----------- ---------- ----------- 
3           09010203   30
4           08120501   5
5           08120501   1

（所影响的行数为 3 行）
*/

select * from PlanTable
/*
id          PlanId     plannum     PlanDate                                               
----------- ---------- ----------- ------------------------------------------------------ 
2           09010203   30          2009-01-02 00:00:00.000
3           08120501   10          2008-12-04 00:00:00.000
4           08090601   11          2008-09-01 00:00:00.000

（所影响的行数为 3 行）
*/

drop table inTable , OutTable , PlanTable , tb

[mailto520]

没有人会？