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

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

select * from #t2
go

drop table #t1,#t2,#Sum

------解决方案--------------------
/*

*/

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,

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
1 2007-07 3.00
2 2007-07 -2.00
2 2007-08 -10.00