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

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

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 '

declare   @i   decimal(18,2),@j   decimal(18,2),@id   varchar(10)
select   @i   =   isnull(sum(加班),0)   from   #t1   where   convert(varchar(7),date,120)=@yearmonth
select   @j   =   isnull(sum(請假),0)   from   #t2   where   convert(varchar(7),date,120)=@yearmonth
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

update   #t2   set   請假   =   0   where   convert(varchar(7),date,120)=@yearmonth
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

update   #t1   set   加班   =   0   where   convert(varchar(7),date,120)=@yearmonth
end
else
begin
update   #t1     set   加班   =0   where   convert(varchar(7),date,120)=@yearmonth
update   #t2   set   請假   =   0   where   convert(varchar(7),date,120)=@yearmonth
end

go
select   *   from   #t1
select   *   from   #t2
go
drop   table   #t1,#t2

------解决方案--------------------
--看看是不是这样,用游标解决

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 '