paoluo(一天到晚游泳的鱼) 还在吗？今天下午的问题，数据库结构改了，不知道怎么弄了。帮忙
--创建测试环境
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 ', '吴七 ', '制作人 '
go
create 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
/*
pd_date kh_name cxht_bhpgbhzzbwzzbw_mxsj gsde sjwgsj xmfzr(项目负责人工时费) zzr(制作人工时费)gsf
2007-07-18 11:28 苏良良 栏板半挂车 HTZZ-52HTZZ-52冲压冲压下料1 4.00 1900-01-01张三(1组)5元,赵六(1组)5元 李四(1组)5元,王五(1组)5元,吴七(2组)20元 80.00
2007-07-18 12:28 苏良良 板半挂车 HTZZ-52HTZZ-52冲压折弯1 3.00 1900-01-01 张三(1组)30元 李四(1组)30元,王五(1组)20元 100.00
*/
/*
我晕，数据库结构改变了，在原来的结构上去掉了xmfzr(项目负责人),xmfzrgsf(项目负责人工时费)两个字段，新增加了一个type(职工类别)字段
用来区别xmfzr（项目负责人）和zzr（制作人），请问下面的SQL应该怎样改写，才能得到上面的记录。
*/
GO
--创建函数
Create Function F_Get_pgd_emp(@ht_bh varchar(50), @zzbw varchar(50), @zzbw_mx varchar(50), @Flag Bit)
Returns Varchar(8000)
As
Begin
Declare @S Varchar(8000)
Select @S = ' '
If(@Flag = 0)
Select @S = @S + ', ' + B.emp_name + '( ' + xz + ') ' + Cast(Cast(xmfzrgsf As Int) As Varchar) + '元 ' From test_pgd1 A
Inner Join test_pgd_emp B On A.xmfzr = B.emp_id
Where ht_bh = @ht_bh And zzbw = @zzbw And zzbw_mx = @zzbw_mx And xmfzrgsf Is Not Null
Order By xmfzr
Else
Select @S = @S + ', ' + B.emp_name + '( ' + xz + ') ' + Cast(Cast(zzrgsf As Int) As Varchar) + '元 ' From test_pgd1 A
Inner Join test_pgd_emp B On A.zzr = B.emp_id
Where ht_bh = @ht_bh And zzbw = @zzbw And zzbw_mx = @zzbw_mx And zzrgsf Is Not Null
Order By xmfzr
Select @S = Stuff(@S, 1, 1, ' ')
Return @S
End
GO
--测试
Select
Max(pd_date) As pd_date,
Max(kh_name) As kh_name,
Max(cx) As cx,
ht_bh,
Max(pgbh) As pgbh,
zzbw,
zzbw_mx,
Max(sj) As sj,
Max(gsde) As gsde,
Max(sjwgsi) As sjwgsi,
dbo.F_Get_pgd_emp(ht_bh, zzbw, zzbw_mx, 0) As xmfzr,
dbo.F_Get_pgd_emp(ht_bh, zzbw, zzbw_mx, 1) As zzr,
Max(gsf) As gsf
From
test_pgd1
Group By
ht_bh,
zzbw,
zzbw_mx
Order By
pd_date
GO
--删除测试环境
Drop Function F_Get_pgd_emp
drop table test_pgd_emp
drop table test_pgd1
------解决方案--------------------
貌似魚老大下班了,我來幫你改下吧,其實只需要把函數修改一下就可以了,調用方法不變
--修改函數
Alter Function F_Get_pgd_emp(@ht_bh varchar(50), @zzbw varchar(50), @zzbw_mx varchar(50), @Flag Bit)
Returns Varchar(8000)
As
Begin
Declare @S Varchar(8000)
Select @S = ' '
If(@Flag = 0)
Select @S = @S + ', ' + B.emp_name + '( ' + xz + ') ' + Ltrim(Cast(zzrgsf As Int)) + '元 ' From test_pgd1 A
Inner Join test_pgd_emp B On A.zzr = B.emp_id
Where ht_bh = @ht_bh And zzbw = @zzbw And zzbw_mx = @zzbw_mx And type = '项目负责人 ' And zzrgsf Is Not Null
Order By zzr
Else
Select @S = @S + ', ' + B.emp_name + '( ' + xz + ') ' + Ltrim(Cast(zzrgsf As Int)) + '元 ' From test_pgd1 A
Inner Join test_pgd_emp B On A.zzr = B.emp_id
Where ht_bh = @ht_bh And zzbw = @zzbw And zzbw_mx = @zzbw_mx And type = '制作人 ' And zzrgsf Is Not Null
Order By zzr
Select @S = Stuff(@S, 1, 1, ' ')
Return @S
End
GO
[解决办法]
--改下函数可以了
--创建函数
Create Function F_Get_pgd_emp(@ht_bh varchar(50), @zzbw varchar(50), @zzbw_mx varchar(50), @Flag Bit)
Returns Varchar(8000)
As
Begin
Declare @S Varchar(8000)
Select @S = ' '
If(@Flag = 0)
Select @S = @S + ', ' + B.emp_name + '( ' + xz + ') ' + Cast(Cast(zzrgsf As Int) As Varchar) + '元 ' From test_pgd1 A
Inner Join test_pgd_emp B On A.zzr = B.emp_id
Where ht_bh = @ht_bh And zzbw = @zzbw And zzbw_mx = @zzbw_mx And zzrgsf Is Not Null and type= '项目负责人 '
Order By xmfzr
Else
Select @S = @S + ', ' + B.emp_name + '( ' + xz + ') ' + Cast(Cast(zzrgsf As Int) As Varchar) + '元 ' From test_pgd1 A
Inner Join test_pgd_emp B On A.zzr = B.emp_id
Where ht_bh = @ht_bh And zzbw = @zzbw And zzbw_mx = @zzbw_mx And zzrgsf Is Not Null and type= '制作人 '
Order By xmfzr
Select @S = Stuff(@S, 1, 1, ' ')
Return @S
End
GO
--测试
Select
Max(pd_date) As pd_date,
Max(kh_name) As kh_name,
Max(cx) As cx,
ht_bh,
Max(pgbh) As pgbh,
zzbw,
zzbw_mx,
Max(sj) As sj,
Max(gsde) As gsde,
Max(sjwgsi) As sjwgsi,
dbo.F_Get_pgd_emp(ht_bh, zzbw, zzbw_mx, 0) As xmfzr,
dbo.F_Get_pgd_emp(ht_bh, zzbw, zzbw_mx, 1) As zzr,
Max(gsf) As gsf
From
test_pgd1
Group By
ht_bh,
zzbw,
zzbw_mx
Order By
pd_date
[解决办法]
修改後的如下
--创建函数
Create Function F_Get_pgd_emp(@ht_bh varchar(50), @zzbw varchar(50), @zzbw_mx varchar(50), @Flag Bit)
Returns Varchar(8000)
As
Begin
Declare @S Varchar(8000)
Select @S = ' '
If(@Flag = 0)
Select @S = @S + ', ' + B.emp_name + '( ' + xz + ') ' + Cast(Cast(zzrgsf As Int) As Varchar) + '元 ' From test_pgd1 A
Inner Join test_pgd_emp B On A.zzr = B.emp_id
Where ht_bh = @ht_bh And zzbw = @zzbw And zzbw_mx = @zzbw_mx And zzrgsf Is Not Null And A.type = '项目负责人 '
Order By zzr
Else
Select @S = @S + ', ' + B.emp_name + '( ' + xz + ') ' + Cast(Cast(zzrgsf As Int) As Varchar) + '元 ' From test_pgd1 A
Inner Join test_pgd_emp B On A.zzr = B.emp_id
Where ht_bh = @ht_bh And zzbw = @zzbw And zzbw_mx = @zzbw_mx And zzrgsf Is Not Null And A.type = '制作人 '
Order By zzr
Select @S = Stuff(@S, 1, 1, ' ')
Return @S
End
GO
--测试
Select
Max(pd_date) As pd_date,
Max(kh_name) As kh_name,
Max(cx) As cx,
ht_bh,
Max(pgbh) As pgbh,
zzbw,
zzbw_mx,
Max(sj) As sj,
Max(gsde) As gsde,
Max(sjwgsi) As sjwgsi,
dbo.F_Get_pgd_emp(ht_bh, zzbw, zzbw_mx, 0) As xmfzr,
dbo.F_Get_pgd_emp(ht_bh, zzbw, zzbw_mx, 1) As zzr,
Max(gsf) As gsf
From
test_pgd1
Group By
ht_bh,
zzbw,
zzbw_mx
Order By
pd_date
GO
