请问这个函数要如何写????

Question

create table t_yan(sheet_no varchar(20),part_no varchar(20))
insert into t_yan select 'BL1C040526001','10006321'
insert into t_yan select 'BL1C040526001','10006331'
insert into t_yan select 'BL1C040526001','O0000000000000002007'
insert into t_yan select 'BL1C040526001','10006801'
insert into t_yan select 'BL1C040526001','10006811'
insert into t_yan select 'BL1C040526001','10006831'
insert into t_yan select 'BL1C040526001','10000072'
insert into t_yan select 'BL1C040526001','10047562'
go
create function yan_f_lot(@sheet_no varchar(20),@part_no varchar(20))
returns varchar(30)
as
begin
    declare @ret varchar(20)
    declare @t table(id int identity(1,1),sheet_no varchar(20),part_no varchar(20))    insert into @t select * from t_yan where sheet_no=@sheet_no    select 
        @ret=sheet_no+'_'+right('000'+rtrim(id),3) 
    from 
        @t
    where
        sheet_no=@sheet_no and part_no=@part_no    return @ret
end
go
select sheet_no,dbo.yan_f_lot(sheet_no,part_no)as sheet_lot,part_no from t_yan 
go/*
sheet_no             sheet_lot                      part_no              
-------------------- ------------------------------ -------------------- 
BL1C040526001        BL1C040526001_001              10006321
BL1C040526001        BL1C040526001_002              10006331
BL1C040526001        BL1C040526001_003              O0000000000000002007
BL1C040526001        BL1C040526001_004              10006801
BL1C040526001        BL1C040526001_005              10006811
BL1C040526001        BL1C040526001_006              10006831
BL1C040526001        BL1C040526001_007              10000072
BL1C040526001        BL1C040526001_008              10047562
*/drop function yan_f_lot
drop table t_yan

Answer 1

楼主说的函数(dbo.yan_f_lot(sheet_no))是不行的,此函数只一个参数根本无法区分SHEET_NO相同的记录.

Answer 2

wgsasd311(自强不息) :
  用游标可以吗？

Answer 3

游标可以,但速度不行.
select * ,identity(int,1,1) id into # from tb order by sheet_no,part_no
select sheet_no,
sheet_lot=sheet_no+right(power(10,3)+(select count(1) from # where id<=a.id),3),
part_no
from #