给出第几周,如16,17周,如何查询求得如下格式?
周表:
工号 姓 名 加班状况(天) 周次 考核分数
001 张三 2 16 8
002 王五 1 16 6
003 李四 1 16 7
001 张三 3 17 10
002 王五 2 17 8
003 李四 1 17 7
然后给出参数周次(16,17),如何查询得出以下格式?请高手帮忙,给出答案,马上给分....
工号 姓名 第16周考核分数 第17周考核分数 两周平均分数 两周加班数
001 张三 8 10 9 5
002 王五 6 8 7 3
003 李四 7 7 7 2
[解决办法]
create table test(工号 varchar(10),姓名 varchar(10),[加班状况(天)] int,周次 int,考核分数 int)
insert test select '001 ', '张三 ',2,16,8
union all select '002 ', '王五 ',1,16,6
union all select '003 ', '李四 ',1,16,7
union all select '001 ', '张三 ',3,17,10
union all select '002 ', '王五 ',2,17,8
union all select '003 ', '李四 ',1,17,7
select 工号,姓名,[第16周考核分数],[第17周考核分数],[两周平均分数]=([第16周考核分数]+[第17周考核分数])/2,
[两周加班数] from
(
select 工号,姓名,[第16周考核分数]=sum(case 周次 when 16 then 考核分数 else 0 end),
[第17周考核分数]=sum(case 周次 when 17 then 考核分数 else 0 end),[两周加班数]=sum([加班状况(天)]) from test
group by 工号,姓名
)a
order by a.工号
[解决办法]
select isnull(A.工号,B.工号),isnull(A.姓名,B.姓名),A.考核分数 第16周考核分数,
B.考核分数 第17周考核分数,(isnull(A.考核分数,0)+isnull(B.考核分数,0))/2 两周平均分数,
(isnull(A.[加班状况(天)],0)+isnull(B.[加班状况(天)],0))/2 两周加班数
from 周表 A full join 周表 B on A.工号=B.工号 and A.周次=16 and B.周次=17
[解决办法]
不好意思，写错一个地方
create table tb (工号 char(3),姓名 char(4),加班状况 int, 周次 int ,考核分数 int)
go
create proc pro_test1 @z1 int,@z2 int
as
begin
declare @sql varchar(8000)
set @sql =
'select a.工号,a.姓名,isnull(a.考核分数,0) as 第 '+cast(@z1 as varchar)+ '周考核分数,isnull(b.考核分数,0) 第 '+cast(@z2 as varchar)+ '周考核分数,(isnull(b.考核分数,0)+ isnull(a.考核分数,0))/2 两周平均分数 ,isnull(a.加班状况,0)+isnull(b.加班状况,0) 两周加班数
from (select * from tb where 周次 = '+cast(@z1 as varchar)+ ') a
left join
(select * from tb where 周次 = '+cast(@z2 as varchar)+ ') b on a.工号 = b.工号 '
exec(@sql)
end
go
insert tb
select '001 ', '张三 ',2,16 , 8 union
select '002 ', '王五 ',1,16 , 6 union
select '003 ', '李四 ',1, 16 , 7 union
select '001 ', '张三 ',3 , 17 , 10 union
select '002 ', '王五 ',2 , 17 , 8 union
select '003 ', '李四 ',1, 17 , 7
declare @z1 int,@z2 int
set @z1 = 16
set @z2 = 17
declare @sql varchar(8000)
set @sql =
'select a.工号,a.姓名,isnull(a.考核分数,0) as 第 '+cast(@z1 as varchar)+ '周考核分数,isnull(b.考核分数,0) 第 '+cast(@z2 as varchar)+ '周考核分数,(isnull(b.考核分数,0)+ isnull(a.考核分数,0))/2 两周平均分数 ,isnull(a.加班状况,0)+isnull(b.加班状况,0) 两周加班数
from (select * from tb where 周次 = '+cast(@z1 as varchar)+ ') a
left join
(select * from tb where 周次 = '+cast(@z2 as varchar)+ ') b on a.工号 = b.工号 '
--1
exec (@sql)
--2
exec pro_test1 16,18 --- 16,17
/*
工号 姓名 第16周考核分数 第17周考核分数 两周平均分数 两周加班数
---- ---- ----------- ----------- ----------- -----------
001 张三 8 10 9 5
002 王五 6 8 7 3
003 李四 7 7 7 2
工号 姓名 第16周考核分数 第18周考核分数 两周平均分数 两周加班数
---- ---- ----------- ----------- ----------- -----------
001 张三 8 0 4 2
002 王五 6 0 3 1
003 李四 7 0 3 1
*/
drop table tb
drop proc pro_test1
[解决办法]
发现一个错误 ，不好意思
create table tb (工号 char(3),姓名 char(4),加班状况 int, 周次 int ,考核分数 int)
go
create proc pro_test1 @z1 varchar(100)
as
begin
declare @sql varchar(8000)
select 1 as id into #d from sysobjects where 1=2
set @z1 = 'insert #d select '+ replace(@z1, ', ', ' union select ')
exec (@z1)
set @sql = 'select 工号,姓名 '
select @sql = @sql +
',sum(case 周次 when '+cast(id as varchar)+ ' then isnull(考核分数,0) else 0 end) as 第 '+cast(id as varchar)+ '周考核分数 '
from #d
exec(@sql + ' ,avg(考核分数) 考核平均分数 ,sum(加班状况) 加班数 from tb where 周次 in (select id from #d) group by 工号,姓名 order by 工号 ')
drop table #d
end
go
insert tb
select '001 ', '张三 ',2,16 , 8 union
select '002 ', '王五 ',1,16 , 6 union
select '003 ', '李四 ',1, 16 , 7 union
select '001 ', '张三 ',3 , 17 , 10 union
select '002 ', '王五 ',2 , 17 , 8 union
select '003 ', '李四 ',1, 17 , 7 union
select '001 ', '张三 ',3 , 18 , 10 union
select '002 ', '王五 ',2 , 18 , 8 union
select '003 ', '李四 ',1, 18 , 7
--2
exec pro_test1 '16,17,18 '
/*
工号 姓名 第16周考核分数 第17周考核分数 第18周考核分数 考核平均分数 加班数
---- ---- ----------- ----------- ----------- ----------- -----------
001 张三 8 10 10 9 8
002 王五 6 8 8 7 5
003 李四 7 7 7 7 3
*/
drop table tb,#d
drop proc pro_test1