请看一段Sql代码,要求按ID进行计算
我想按照ID号分别计算每个人的加班和请假,请运行下面代码,就明白我想怎样了.因为运行下面代码,如果不加ID的条件,就会按照日期一直计算下去,但加ID条件一次只能计算一个,请问怎样才能自动按照每个人去计算呢?谢谢!!!
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 '
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
select * from #t2
go
drop table #t1,#t2,#Sum
[解决办法]
/*
不知道同一ID有多次加班或请假的情况怎么UPDATE，下面给出当月各ID的加班和请假情况：
*/
create table #t1(ID int,Date smalldatetime,加班 decimal(18,2))
insert #t1(ID,Date,加班)
select '1 ', '2007-07-01 ', '5 ' union all
select '1 ', '2007-07-03 ', '5 ' union all
select '3 ', '2007-07-08 ', '5 ' union all
select '4 ', '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 '6 ', '2007-07-09 ', '10 ' union all
select '2 ', '2007-08-22 ', '10 '
go
declare @yearmonth varchar(50)
set @yearmonth= '2007-07 '
select ID = case when a.ID is null then b.ID else a.ID end,
加班 = isnull(a.加班, 0),
請假 = isnull(b.請假, 0),
差额 = isnull(a.加班, 0) - isnull(b.請假, 0)
from
(select ID, 加班 = sum(加班) from #t1 where convert(varchar(7),date,120)=@yearmonth group by ID) a full join
(select ID, 請假 = sum(請假) from #t2 where convert(varchar(7),date,120)=@yearmonth group by ID) b
on a.id = b.id
/*
ID 加班 請假 差额
-- ------ ----- ------
1 10.00 10.00 .00
3 5.00 .00 5.00
4 5.00 .00 5.00
6 .00 10.00 -10.00
（所影响的行数为 4 行）
*/
drop table #t1,#t2
[解决办法]
根据楼上给的数据,我也做了一个,不知道是否符合楼主的意思:
A.建设表:T1,T2的ID,Date是否应该做为主键?
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 ' union all
select '1 ', '2007-07-15 ', '6 ' union all
select '1 ', '2007-07-20 ', '4 ' union all
select '2 ', '2007-07-20 ', '8 '
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 ' union all
select '1 ', '2007-07-8 ', '2 ' union all
select '2 ', '2007-07-21 ', '10 ' union all
select '2 ', '2007-07-22 ', '5 '
B.SQL文实现:
select ID,date,sum(Value) value from (
select ID,convert(varchar(7),date,120) Date,SUM(加班) as Value from T1 group by (convert(varchar(7),date,120)),ID
union all
select ID,convert(varchar(7),date,120) Date,SUM(請假)*-1 as Value from T2 group by (convert(varchar(7),date,120)),ID
) as ttt
group by (date),ID
C.结果:
ID Date value
12007-073.00
22007-07-2.00
22007-08-10.00
