--创建测试环境
create table test_pgd_emp
(
emp_id varchar(10),
emp_name varchar(20),
emp_type varchar(20)
)
insert into test_pgd_emp select '00001','张三','项目负责人'
insert into test_pgd_emp select '00002','李四','制作人'
insert into test_pgd_emp select '00003','王五','制作人'
insert into test_pgd_emp select '00004','赵六','项目负责人'
insert into test_pgd_emp select '00005','吴七','制作人'gocreate table test_pgd1 --派工单测试表
(
pd_date datetime,--派工日期
kh_name varchar(50),--客户姓名
cx varchar(50),--车型
ht_bh varchar(50),--合同编号
zzbw varchar(50),--制作部位
zzbw_mx varchar(50),--制作部位明细
sj int,--数量
gsde decimal(13,2),--工时定额
sjwgsi datetime,--实际完工时间
zzr varchar(50),--制作人
zzrgsf decimal(13,2),--制作人工时费
gsf decimal(13,2),--合计工时费
xz varchar(50),-- 小组
pgbh varchar(50),--派工单编号
type varchar(50)--职工类别
)
insert into test_pgd1 select '2007-7-18 11:28:00','苏良良','栏板半挂车','HTZZ-52','冲压','冲压下料',1,4,'','00001',5,80,'1组','HTZZ-52','项目负责人'
insert into test_pgd1 select '2007-7-18 11:28:00','苏良良','栏板半挂车','HTZZ-52','冲压','冲压下料',1,4,'','00002',5,80,'1组','HTZZ-52','制作人'
insert into test_pgd1 select '2007-7-18 11:28:00','苏良良','栏板半挂车','HTZZ-52','冲压','冲压下料',1,4,'','00003',5,80,'1组','HTZZ-52','制作人'
insert into test_pgd1 select '2007-7-18 11:28:00','苏良良','栏板半挂车','HTZZ-52','冲压','冲压下料',1,4,'','00004',5,80,'1组','HTZZ-52','项目负责人'
insert into test_pgd1 select '2007-7-18 11:28:00','苏良良','栏板半挂车','HTZZ-52','冲压','冲压下料',1,4,'','00005',20,80,'2组','HTZZ-52','制作人'
insert into test_pgd1 select '2007-7-18 12:28:00','苏良良','栏板半挂车','HTZZ-52','冲压','折弯',1,3,'','00001',30,100,'1组','HTZZ-52','项目负责人'
insert into test_pgd1 select '2007-7-18 12:28:00','苏良良','栏板半挂车','HTZZ-52','冲压','折弯',1,3,'','00002',30,100,'1组','HTZZ-52','制作人'
insert into test_pgd1 select '2007-7-18 12:28:00','苏良良','栏板半挂车','HTZZ-52','冲压','折弯',1,3,'','00003',20,100,'1组','HTZZ-52','制作人'
insert into test_pgd1 select '2007-7-18 12:28:00','苏良良','栏板半挂车','HTZZ-52','冲压','折弯',1,3,'','00004',null,100,'1组','HTZZ-52','项目负责人'
insert into test_pgd1 select '2007-7-18 12:28:00','苏良良','栏板半挂车','HTZZ-52','冲压','折弯',1,3,'','00005',null,100,'2组','HTZZ-52','制作人'select * from test_pgd1
/*
要得到以下报表
ht_bh 职工姓名 pd_date 冲压下料(工时费) 折弯工时费
HTZZ-52 张三(1组) 2007-7-18 5 30
HTZZ-52 李四(1组) 2007-7-18 5 30
HTZZ-52 王五(1组) 2007-7-18 5 20
HTZZ-52 赵六(1组) 2007-7-18 5 0
HTZZ-52 吴七(2组) 2007-7-18 20 0
*/--要能够按照xz,zzr,pd_date进行查询
GOdrop table test_pgd_emp
drop table test_pgd1
create table test_pgd_emp
(
emp_id varchar(10),
emp_name varchar(20),
emp_type varchar(20)
)
insert into test_pgd_emp select '00001','张三','项目负责人'
insert into test_pgd_emp select '00002','李四','制作人'
insert into test_pgd_emp select '00003','王五','制作人'
insert into test_pgd_emp select '00004','赵六','项目负责人'
insert into test_pgd_emp select '00005','吴七','制作人'gocreate table test_pgd1 --派工单测试表
(
pd_date datetime,--派工日期
kh_name varchar(50),--客户姓名
cx varchar(50),--车型
ht_bh varchar(50),--合同编号
zzbw varchar(50),--制作部位
zzbw_mx varchar(50),--制作部位明细
sj int,--数量
gsde decimal(13,2),--工时定额
sjwgsi datetime,--实际完工时间
zzr varchar(50),--制作人
zzrgsf decimal(13,2),--制作人工时费
gsf decimal(13,2),--合计工时费
xz varchar(50),-- 小组
pgbh varchar(50),--派工单编号
type varchar(50)--职工类别
)
insert into test_pgd1 select '2007-7-18 11:28:00','苏良良','栏板半挂车','HTZZ-52','冲压','冲压下料',1,4,'','00001',5,80,'1组','HTZZ-52','项目负责人'
insert into test_pgd1 select '2007-7-18 11:28:00','苏良良','栏板半挂车','HTZZ-52','冲压','冲压下料',1,4,'','00002',5,80,'1组','HTZZ-52','制作人'
insert into test_pgd1 select '2007-7-18 11:28:00','苏良良','栏板半挂车','HTZZ-52','冲压','冲压下料',1,4,'','00003',5,80,'1组','HTZZ-52','制作人'
insert into test_pgd1 select '2007-7-18 11:28:00','苏良良','栏板半挂车','HTZZ-52','冲压','冲压下料',1,4,'','00004',5,80,'1组','HTZZ-52','项目负责人'
insert into test_pgd1 select '2007-7-18 11:28:00','苏良良','栏板半挂车','HTZZ-52','冲压','冲压下料',1,4,'','00005',20,80,'2组','HTZZ-52','制作人'
insert into test_pgd1 select '2007-7-18 12:28:00','苏良良','栏板半挂车','HTZZ-52','冲压','折弯',1,3,'','00001',30,100,'1组','HTZZ-52','项目负责人'
insert into test_pgd1 select '2007-7-18 12:28:00','苏良良','栏板半挂车','HTZZ-52','冲压','折弯',1,3,'','00002',30,100,'1组','HTZZ-52','制作人'
insert into test_pgd1 select '2007-7-18 12:28:00','苏良良','栏板半挂车','HTZZ-52','冲压','折弯',1,3,'','00003',20,100,'1组','HTZZ-52','制作人'
insert into test_pgd1 select '2007-7-18 12:28:00','苏良良','栏板半挂车','HTZZ-52','冲压','折弯',1,3,'','00004',null,100,'1组','HTZZ-52','项目负责人'
insert into test_pgd1 select '2007-7-18 12:28:00','苏良良','栏板半挂车','HTZZ-52','冲压','折弯',1,3,'','00005',null,100,'2组','HTZZ-52','制作人'select * from test_pgd1
/*
要得到以下报表
ht_bh 职工姓名 pd_date 冲压下料(工时费) 折弯工时费
HTZZ-52 张三(1组) 2007-7-18 5 30
HTZZ-52 李四(1组) 2007-7-18 5 30
HTZZ-52 王五(1组) 2007-7-18 5 20
HTZZ-52 赵六(1组) 2007-7-18 5 0
HTZZ-52 吴七(2组) 2007-7-18 20 0
*/--要能够按照xz,zzr,pd_date进行查询
GOdrop table test_pgd_emp
drop table test_pgd1
b.emp_name+a.xz as '职工姓名',
convert(char(10),a.pd_date,120) as 'pd_date',
sum(case when a.zzbw_mx='冲压下料' then a.sj*isnull(a.zzrgsf,0) else 0 end) as '冲压下料(工时费)',
sum(case when a.zzbw_mx='折弯' then a.sj*isnull(a.zzrgsf,0) else 0 end) as '折弯工时费'
from test_pgd1 a
inner join test_pgd_emp b on a.zzr=b.emp_id
group by a.ht_bh,
b.emp_name+a.xz,
convert(char(10),a.pd_date,120)
/*
要得到以下报表
ht_bh 职工姓名 pd_date 冲压下料(工时费) 折弯工时费
HTZZ-52 张三(1组) 2007-7-18 5 30
HTZZ-52 李四(1组) 2007-7-18 5 30
HTZZ-52 王五(1组) 2007-7-18 5 20
HTZZ-52 赵六(1组) 2007-7-18 5 0
HTZZ-52 吴七(2组) 2007-7-18 20 0
*/
max(case zzbw_mx when '冲压下料' then zzrgsf else 0 end ) as 冲压下料工时费,
max(case zzbw_mx when '折弯' then zzrgsf else 0 end) as 折弯工时费
from test_pgd1 a inner join test_pgd_emp b on a.zzr=b.emp_id group by ht_bh,emp_name+'('+xz+')',convert(varchar(10),a.pd_date,120)
/*
ht_bh 职工姓名 pd_date 冲压下料工时费 折弯工时费
------------------------------------------------------------
HTZZ-52 李四(1组) 2007-07-18 5.00 30.00
HTZZ-52 王五(1组) 2007-07-18 5.00 20.00
HTZZ-52 吴七(2组) 2007-07-18 20.00 .00
HTZZ-52 张三(1组) 2007-07-18 5.00 30.00
HTZZ-52 赵六(1组) 2007-07-18 5.00 .00
*/
------------------------------------------要是不用固定字段名,用动态产生的方式该怎样写?
Select @S = ' Select A.ht_bh, B.emp_name + ''('' + A.xz + '')'' As 职工姓名, Convert(Varchar(10), A.pd_date, 120) As pd_date'
Select @S = @S + ' , SUM(Case zzbw_mx When ''' + zzbw_mx + ''' Then zzrgsf Else 0 End ) As [' + zzbw_mx + '工时费]'
From test_pgd1 Group By zzbw_mx
Select @S = @S + ' From test_pgd1 A Inner Join test_pgd_emp B On A.zzr = B.emp_id Group By ht_bh, emp_name + ''('' + xz + '')'', Convert(Varchar(10), A.pd_date,120), B.emp_id Order By B.emp_id'
EXEC(@S)
sum(case when a.zzbw_mx='冲压下料' then a.sj*isnull(a.zzrgsf,0) else 0 end) as '冲压下料(工时费)',
------------------------------------------要是不用固定字段名,用动态产生的方式该怎样写?
-------------------------------------------------------------declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',sum(case when a.zzbw_mx='''+zzbw_mx+''' then a.sj*isnull(a.zzrgsf,0) else 0 end) as '''+zzbw_mx+'工时费'''
from (select distinct zzbw_mx from test_pgd1)t
exec('select a.ht_bh,
b.emp_name+a.xz as ''职工姓名'',
convert(char(10),a.pd_date,120) as ''pd_date'''+@sql+'
from test_pgd1 a
inner join test_pgd_emp b on a.zzr=b.emp_id
group by a.ht_bh,
b.emp_name+a.xz,
convert(char(10),a.pd_date,120)')
Select
A.ht_bh,
B.emp_name + '(' + A.xz + ')' As 职工姓名,
Convert(Varchar(10), A.pd_date, 120) As pd_date,
SUM(Case zzbw_mx When '冲压下料' Then zzrgsf Else 0 End ) As 冲压下料工时费,
SUM(Case zzbw_mx When '折弯' Then zzrgsf Else 0 End) As 折弯工时费
From
test_pgd1 A
Inner Join
test_pgd_emp B
On A.zzr = B.emp_id
Group By
ht_bh, emp_name+'('+xz+')', Convert(Varchar(10), A.pd_date,120), B.emp_id
Order By
B.emp_id--如果zzbw_mx不是固定的
Declare @S Varchar(8000)
Select @S = ' Select A.ht_bh, B.emp_name + ''('' + A.xz + '')'' As 职工姓名, Convert(Varchar(10), A.pd_date, 120) As pd_date'
Select @S = @S + ' , SUM(Case zzbw_mx When ''' + zzbw_mx + ''' Then zzrgsf Else 0 End ) As [' + zzbw_mx + '工时费]'
From test_pgd1 Group By zzbw_mx
Select @S = @S + ' From test_pgd1 A Inner Join test_pgd_emp B On A.zzr = B.emp_id Group By ht_bh, emp_name + ''('' + xz + '')'', Convert(Varchar(10), A.pd_date,120), B.emp_id Order By B.emp_id'
EXEC(@S)
create table test_pgd_emp
(
emp_id varchar(10),
emp_name varchar(20),
emp_type varchar(20)
)
insert into test_pgd_emp select '00001','张三','项目负责人'
insert into test_pgd_emp select '00002','李四','制作人'
insert into test_pgd_emp select '00003','王五','制作人'
insert into test_pgd_emp select '00004','赵六','项目负责人'
insert into test_pgd_emp select '00005','吴七','制作人'gocreate table test_pgd1 --派工单测试表
(
pd_date datetime,--派工日期
kh_name varchar(50),--客户姓名
cx varchar(50),--车型
ht_bh varchar(50),--合同编号
zzbw varchar(50),--制作部位
zzbw_mx varchar(50),--制作部位明细
sj int,--数量
gsde decimal(13,2),--工时定额
sjwgsi datetime,--实际完工时间
zzr varchar(50),--制作人
zzrgsf decimal(13,2),--制作人工时费
gsf decimal(13,2),--合计工时费
xz varchar(50),-- 小组
pgbh varchar(50),--派工单编号
type varchar(50)--职工类别
)
insert into test_pgd1 select '2007-7-18 11:28:00','苏良良','栏板半挂车','HTZZ-52','冲压','冲压下料',1,4,'','00001',5,80,'1组','HTZZ-52','项目负责人'
insert into test_pgd1 select '2007-7-18 11:28:00','苏良良','栏板半挂车','HTZZ-52','冲压','冲压下料',1,4,'','00002',5,80,'1组','HTZZ-52','制作人'
insert into test_pgd1 select '2007-7-18 11:28:00','苏良良','栏板半挂车','HTZZ-52','冲压','冲压下料',1,4,'','00003',5,80,'1组','HTZZ-52','制作人'
insert into test_pgd1 select '2007-7-18 11:28:00','苏良良','栏板半挂车','HTZZ-52','冲压','冲压下料',1,4,'','00004',5,80,'1组','HTZZ-52','项目负责人'
insert into test_pgd1 select '2007-7-18 11:28:00','苏良良','栏板半挂车','HTZZ-52','冲压','冲压下料',1,4,'','00005',20,80,'2组','HTZZ-52','制作人'
insert into test_pgd1 select '2007-7-18 12:28:00','苏良良','栏板半挂车','HTZZ-52','冲压','折弯',1,3,'','00001',30,100,'1组','HTZZ-52','项目负责人'
insert into test_pgd1 select '2007-7-18 12:28:00','苏良良','栏板半挂车','HTZZ-52','冲压','折弯',1,3,'','00002',30,100,'1组','HTZZ-52','制作人'
insert into test_pgd1 select '2007-7-18 12:28:00','苏良良','栏板半挂车','HTZZ-52','冲压','折弯',1,3,'','00003',20,100,'1组','HTZZ-52','制作人'
insert into test_pgd1 select '2007-7-18 12:28:00','苏良良','栏板半挂车','HTZZ-52','冲压','折弯',1,3,'','00004',null,100,'1组','HTZZ-52','项目负责人'
insert into test_pgd1 select '2007-7-18 12:28:00','苏良良','栏板半挂车','HTZZ-52','冲压','折弯',1,3,'','00005',null,100,'2组','HTZZ-52','制作人'--select * from test_pgd1
GO
--如果zzbw_mx是固定的
Select
A.ht_bh,
B.emp_name + '(' + A.xz + ')' As 职工姓名,
Convert(Varchar(10), A.pd_date, 120) As pd_date,
SUM(Case zzbw_mx When '冲压下料' Then zzrgsf Else 0 End ) As 冲压下料工时费,
SUM(Case zzbw_mx When '折弯' Then zzrgsf Else 0 End) As 折弯工时费
From
test_pgd1 A
Inner Join
test_pgd_emp B
On A.zzr = B.emp_id
Group By
ht_bh, emp_name+'('+xz+')', Convert(Varchar(10), A.pd_date,120), B.emp_id
Order By
B.emp_id--如果zzbw_mx不是固定的
Declare @S Varchar(8000)
Select @S = ' Select A.ht_bh, B.emp_name + ''('' + A.xz + '')'' As 职工姓名, Convert(Varchar(10), A.pd_date, 120) As pd_date'
Select @S = @S + ' , SUM(Case zzbw_mx When ''' + zzbw_mx + ''' Then zzrgsf Else 0 End ) As [' + zzbw_mx + '工时费]'
From test_pgd1 Group By zzbw_mx
Select @S = @S + ' From test_pgd1 A Inner Join test_pgd_emp B On A.zzr = B.emp_id Group By ht_bh, emp_name + ''('' + xz + '')'', Convert(Varchar(10), A.pd_date,120), B.emp_id Order By B.emp_id'
EXEC(@S)
GO
drop table test_pgd_emp
drop table test_pgd1
--结果
/*
ht_bh 职工姓名 pd_date 冲压下料工时费 折弯工时费
HTZZ-52 张三(1组) 2007-07-18 5.00 30.00
HTZZ-52 李四(1组) 2007-07-18 5.00 30.00
HTZZ-52 王五(1组) 2007-07-18 5.00 20.00
HTZZ-52 赵六(1组) 2007-07-18 5.00 .00
HTZZ-52 吴七(2组) 2007-07-18 20.00 .00
*/