22,207
社区成员
发帖
与我相关
我的任务
分享
create table tb(AUTOID int, Workorderid int, PO varchar(20), WO varchar(20), WaferLot varchar(20), WaferNO varchar(20))
insert into tb values(1, 1, '308967', '08092201', 'AA0908', '01')
insert into tb values(2, 1, '308967', '08092201', 'AA0908', '02')
insert into tb values(3, 1, '308967', '08092201', 'AA0910', '01')
go
--创建一个合并WaferLot的函数
create function f_hb1(@PO varchar(20) , @WO varchar(20))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + '/' + cast(WaferLot as varchar) from (select distinct po , wo , WaferLot from tb) t where PO = @PO and WO = @WO
set @str = right(@str , len(@str) - 1)
return(@str)
End
go
--创建一个合并WaferNO的函数
create function f_hb2(@PO varchar(20) , @WO varchar(20))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + '/' + cast(WaferNO as varchar) from tb where PO = @PO and WO = @WO
set @str = right(@str , len(@str) - 1)
return(@str)
End
go
--调用自定义函数得到结果:
select m.* , n.waferno from
(select distinct po , wo ,dbo.f_hb1(po , wo) as WaferLot from (select distinct po , wo , WaferLot from tb) t) m ,
(select distinct po , wo ,dbo.f_hb2(po , wo) as WaferNO from tb) n
where m.po = n.po and m.wo = n.wo5
drop table tb
drop function dbo.f_hb1
drop function dbo.f_hb2
/*
po wo WaferLot
-------------------- -------------------- --------------
308967 08092201 AA0908/AA0910 01/02/01
(所影响的行数为 1 行)
*/
--建立函数:
create function fn_WaferLot (
@PO varchar(20),
@WO varchar(20)
)
returns varchar(300)
as
begin
declare @r varchar(300)
select @r=isnull(@r+'/','')+WaferLot
from ( select distinct WaferLot from tab where PO=@PO and WO=@WO) as t
return @r
end
go
create function fn_WaferNO (
@PO varchar(20),
@WO varchar(20)
)
returns varchar(300)
as
begin
declare @r varchar(300)
select @r=isnull(@r+'/','')+WaferNO
from tab where PO=@PO and WO=@WO
return @r
end
go
--调用
select PO,WO,dbo.fn_WaferLot(PO,WO) as WaferLot,dbo.fn_WaferNo(PO,WO) as WaferNo
from tab
group by PO,WO