# 请看一段Sql代码,要求按ID进行计算,该怎么解决(2)

www.MyException.Cn  网友分享于：2013-01-31  浏览：11次

go
declare @yearmonth varchar(50)
set @yearmonth= '2007-07 '
declare @id int

declare t_cursor cursor for
select id from #t1 union select id from #t2
open t_cursor
fetch next from t_cursor into @id
while @@fetch_status = 0
begin
declare @i decimal(18,2),@j decimal(18,2)
select @i = isnull(sum(加班),0) from #t1 where convert(varchar(7),date,120)=@yearmonth and id = @id
select @j = isnull(sum(請假),0) from #t2 where convert(varchar(7),date,120)=@yearmonth and id = @id
if @i > @j
begin
update #t1
set @j = @j - 加班
,加班 = case when @j > =0 then 0 when @j < 0 and @j + 加班 > 0 then -@j else 加班 end
where convert(varchar(7),date,120)=@yearmonth and id = @id

update #t2 set 請假 = 0 where convert(varchar(7),date,120)=@yearmonth and id = @id
end
else if @i < @j
begin
update #t2
set @i = @i - 請假
,請假 = case when @i > =0 then 0 when @i < 0 and @i + 請假 > 0 then -@i else 請假 end
where convert(varchar(7),date,120)=@yearmonth and id = @id

update #t1 set 加班 = 0 where convert(varchar(7),date,120)=@yearmonth and id = @id
end
else
begin
update #t1 set 加班 =0 where convert(varchar(7),date,120)=@yearmonth and id = @id
update #t2 set 請假 = 0 where convert(varchar(7),date,120)=@yearmonth and id = @id
end
fetch next from t_cursor into @id
end
close t_cursor
deallocate t_cursor
go
select * from #t1
select * from #t2
go
drop table #t1,#t2

/*
ID Date 加班
----------- ------------------------------------------------------ --------------------
1 2007-07-01 00:00:00 .00
2 2007-07-15 00:00:00 5.00

(2 row(s) affected)

ID Date 請假
----------- ------------------------------------------------------ --------------------
1 2007-07-05 00:00:00 5.00
2 2007-08-22 00:00:00 10.00

(2 row(s) affected)
*/

------解决方案--------------------
create table #t1(ID int,Date smalldatetime,加班 decimal(18,2))
insert #t1(ID,Date,加班)
select '1 ', '2007-07-01 ', '5 ' union all
select '2 ', '2007-07-15 ', '5 '
go
create table #t2(ID int,Date smalldatetime,請假 decimal(18,2))
insert #t2(ID,Date,請假)
select '1 ', '2007-07-05 ', '10 ' union all
select '2 ', '2007-08-22 ', '10 '
go

declare @yearmonth varchar(50)
set @yearmonth= '2007-07 '

select isnull(#t1.id,#t2.id) as id,isnull(sum(加班),0) as 加班 ,isnull(sum(請假),0) as 請假
into #Sum
from #t1 full outer join #t2 on #t1.id=#t2.id
where convert(varchar(7),#t1.date,120)=@yearmonth and convert(varchar(7),#t2.date,120)=@yearmonth
group by isnull(#t1.id,#t2.id)

update #t1 set #t1.加班 =(case when #Sum.加班-#Sum.請假 <0 then 0 else #Sum.加班-#Sum.請假 end)
from #t1 inner join #Sum on #t1.id=#Sum.id
where convert(varchar(7),date,120)=@yearmonth

update #t2 set #t2.請假 =(case when #Sum.請假-#Sum.加班 <0 then 0 else #Sum.請假-#Sum.加班 end )
from #t2 inner join #Sum on #t2.id=#Sum.id
where convert(varchar(7),date,120)=@yearmonth

go
select * from #t1