有两张表A、B表:
A表:
单据编号 工号 姓名 岗位
140401-001 1903 张三 机长
140401-001 1801 王五 副手
140402-002 1501 李一 副手
140402-002 1801 王五 副手
140402-002 1601 白二 机长
140403-003 1903 张三 机长
B表:
单据编号 项目 完成率 完成数
140401-001 A1565 20 100
140401-001 A1566 30 400
140402-002 B1002 40 600
140403-003 C1750 100 900
两表用单据编号关联,单据编号的前6位代表年月日.B表中的项目完成率及完成数,由A表相同单据编号下的人员生产,要统计出以下结果,每个月每天的完成率及完成数合计
结果要求分表如以下两表结果:
结果一,要求纵向显示结果:
工号 姓名 岗位 完成率 完成数
1903 张三 机长 150 1400
1801 王五 副手 90 1100
1501 李一 副手 40 600
1601 白二 机长 40 600
结果二,要求横向以一个月最大31天,将每人每天完成率及完成数分别统计成两行,以每个月31天为准.那天有生成结果结果显示那天:
工号 姓名 岗位 1号 2号 3号 4号 5号....以至到31号 (每个月31天) 合计
1903 张三 机长 50 100 150
1903 张三 机长 500 900 1400
1801 王五 副手 50 40 90
1801 王五 副手 500 600 1100
1501 李一 副手 40 40
1501 李一 副手 600 600
1601 白二 机长 40 40
1601 白二 机长 600 600
问题一:
declare @A表 table ( 单据编号 varchar(10),工号 varchar(4),姓名 varchar(10),岗位 varchar(10) )
insert @A表 ( 单据编号,工号,姓名, 岗位 )
select '140401-001','1903','张三','机长'
union select '140401-001','1801','王五','副手'
union select '140402-002','1501','李一','副手'
union select '140402-002','1801','王五','副手'
union select '140402-002','1601','白二','机长'
union select '140403-003','1903','张三','机长'
declare @B表 table ( 单据编号 varchar(10),项目 varchar(50),完成率 int,完成数 int )
insert @B表 ( 单据编号,项目,完成率,完成数 )
select '140401-001','A1565',20,100
union select '140401-001','A1566',30,400
union select '140402-002','B1002',40,600
union select '140403-003','C1750',100,900
/*
两表用单据编号关联,单据编号的前6位代表年月日.B表中的项目完成率及完成数,由A表相同单据编号下的人员生产,要统计出以下结果,每个月每天的完成率及完成数合计
结果要求分表如以下两表结果:
结果一,要求纵向显示结果:
工号 姓名 岗位 完成率 完成数
1903 张三 机长 150 1400
1801 王五 副手 90 1100
1501 李一 副手 40 600
1601 白二 机长 40 600
*/
select 工号,姓名,岗位,sum(b.完成率) 完成率,sum(b.完成数) 完成数
from @A表 a,@B表 b
where a.单据编号 = b.单据编号
group by 工号,姓名, 岗位
order by 4 desc
问题二:
declare @c_sql varchar(max)
declare @c_sql1 varchar(max)
declare @d_startdate datetime -- 开始的时间
declare @i int -- 循环变量
declare @c_month varchar(10)
declare @month_name varchar(10)
set @d_startdate = '2014-04-01'
set @c_sql = '
declare @A表 table ( 单据编号 varchar(10),工号 varchar(4),姓名 varchar(10),岗位 varchar(10) )
insert @A表 ( 单据编号,工号,姓名, 岗位 )
select ''140401-001'',''1903'',''张三'',''机长''
union select ''140401-001'',''1801'',''王五'',''副手''
union select ''140402-002'',''1501'',''李一'',''副手''
union select ''140402-002'',''1801'',''王五'',''副手''
union select ''140402-002'',''1601'',''白二'',''机长''
union select ''140403-003'',''1903'',''张三'',''机长''
declare @B表 table ( 单据编号 varchar(10),项目 varchar(50),完成率 int,完成数 int )
insert @B表 ( 单据编号,项目,完成率,完成数 )
select ''140401-001'',''A1565'',20,100
union select ''140401-001'',''A1566'',30,400
union select ''140402-002'',''B1002'',40,600
union select ''140403-003'',''C1750'',100,900
select 工号,姓名,岗位,
'
set @c_sql1 = ' union select 工号,姓名,岗位,'
set @i = 0
while @i < 32
begin
set @c_month = convert(varchar(10),dateadd(dd,@i,@d_startdate),120)
set @month_name = cast(cast( right(@c_month,2) as int ) as varchar(2) ) + '号'
set @c_month = replace(@c_month,'-','')
-- 根据动态语句实现递减
set @c_sql = @c_sql + ' max(case when 时间 = ''' + @c_month + ''' then 完成率 else null end) [' + @month_name + '] ,'
set @c_sql1 = @c_sql1 + ' max(case when 时间 = ''' + @c_month + ''' then 完成数 else null end) ,'
set @i = @i + 1
end
set @c_sql = @c_sql + '
sum(完成率) 合计
from ( select ''20''+left(a.单据编号,6) 时间 ,工号,姓名, 岗位,项目,完成率,完成数
from @A表 a,@B表 b
where a.单据编号 = b.单据编号 ) f
group by 工号,姓名, 岗位
'
set @c_sql1 = @c_sql1 + '
sum(完成数) 合计
from ( select ''20''+left(a.单据编号,6) 时间 ,工号,姓名, 岗位,项目,完成率,完成数
from @A表 a,@B表 b
where a.单据编号 = b.单据编号 ) f
group by 工号,姓名, 岗位
'
exec ( @c_sql + @c_sql1 )