create table #tb(f1 varchar(100)) 
insert into #tb 
select '5,8,9,10,13' union all 
select '5,8,10,12,13'union all 
select '5,9,10,12,13'union all 
select '6,7,8,9,11'  union all 
select '1,3,4,5,11'  union all 
select '2,4,5,6,7'  union all 
select '1,3,5,7,8' 
给定一个字符串如下 
A,N,D,5,9,2,M,Y,-,X,.,+,0,4,S 
要求按照#tb表中f1字段的值对字符串相应位置取值,各个值以空格分开 
对应关系:
比如'5,8,9,10,13'  取字符串A,N,D,5,9,2,M,Y,-,X,.,+,0,4,S 第5,8,9,10,13个位置的对应值 结果  9 Y - X 0 (值与值间加空格)上面字符串的最后结果如下:
9 Y - X 0 
9 Y X + 0 
9 - X + 0 
2 M Y - . 
A D 5 9 . 
A D 9 M Y 
请用sql2000实现

解决方案 »

  1. sql2005语法我不熟悉
    看不懂
    liangCK 
    帮写个sql2000的吧
      

  2. --用个函数来解决.create table tb(f1 varchar(100)) 
    insert into tb 
    select '5,8,9,10,13' union all 
    select '5,8,10,12,13'union all 
    select '5,9,10,12,13'union all 
    select '6,7,8,9,11'  union all 
    select '1,3,4,5,11'  union all 
    select '2,4,5,6,7'  union all 
    select '1,3,5,7,8' 
    gocreate function my_func (@f1 varchar(50))
    RETURNS varchar(50)
    AS
    BEGIN
       declare @str varchar(50)
       declare @rtn varchar(50)
       declare @i as int
       set @str = 'A,N,D,5,9,2,M,Y,-,X,.,+,0,4,S'
       set @rtn = ''
       set @i = 0
       while charindex(',',@f1) > 0
       begin
          set @i = left(@f1 , charindex(',',@f1) - 1)
          set @rtn = @rtn + ' ' + substring(@str , (@i-1)*2+1 , 1)
          set @f1 = substring(@f1 , charindex(',',@f1) + 1 , len(@f1))
       end
       set @i = @f1
       set @rtn = @rtn + ' ' + substring(@str , (@i-1)*2+1 , 1)
       RETURN(@rtn)
    END
    goselect f1 , f2 = dbo.my_func(f1) from tbdrop table tb
    drop function dbo.my_func/*
    f1                                                                                                   f2                                                 
    ---------------------------------------------------------------------------------------------------- -------------------------------------------------- 
    5,8,9,10,13                                                                                           9 Y - X 0
    5,8,10,12,13                                                                                          9 Y X + 0
    5,9,10,12,13                                                                                          9 - X + 0
    6,7,8,9,11                                                                                            2 M Y - .
    1,3,4,5,11                                                                                            A D 5 9 .
    2,4,5,6,7                                                                                             N 5 9 2 M
    1,3,5,7,8                                                                                             A D 9 M Y(所影响的行数为 7 行)*/
      


  3. if object_id('ta') is not null drop table ta
    go
    create table ta(id int identity(1,1),dd varchar(1))
    insert ta values('A')
    insert ta values('N')
    insert ta values('D')
    insert ta values('5')
    insert ta values('9')
    insert ta values('2')
    insert ta values('M')
    insert ta values('Y')
    insert ta values('-')
    insert ta values('X')
    insert ta values('.')
    insert ta values('+')
    insert ta values('0')
    insert ta values('4')
    insert ta values('S')
    go
    select * from ta
    ----创建函数
    create function fn_getString(@str varchar(50))
    returns varchar(50)
    as
    begin
    declare @R varchar(50)
    select @R = isnull(@R,'')+dd from ta where charindex(','+rtrim(id)+',',','[email protected]+',')>0
    return @R
    endselect *,rr = dbo.fn_getString(f1) from #tb
    /*
    f1                                                                                                   rr
    ---------------------------------------------------------------------------------------------------- --------------------------------------------------
    5,8,9,10,13                                                                                          9Y-X0
    5,8,10,12,13                                                                                         9YX+0
    5,9,10,12,13                                                                                         9-X+0
    6,7,8,9,11                                                                                           2MY-.
    1,3,4,5,11                                                                                           AD59.
    2,4,5,6,7                                                                                            N592M
    1,3,5,7,8                                                                                            AD9MY(7 row(s) affected)
    */
      


  4. ----创建函数
    alter function fn_getString(@str varchar(50))
    returns varchar(50)
    as
    begin
    declare @R varchar(50)
    select @R = isnull(@R,'')+dd+' ' from ta where charindex(','+rtrim(id)+',',','[email protected]+',')>0
    return @R
    endselect *,rr = dbo.fn_getString(f1) from #tb
    /*
    f1                                                                                                   rr
    f1                                                                                                   rr
    ---------------------------------------------------------------------------------------------------- --------------------------------------------------
    5,8,9,10,13                                                                                          9 Y - X 0 
    5,8,10,12,13                                                                                         9 Y X + 0 
    5,9,10,12,13                                                                                         9 - X + 0 
    6,7,8,9,11                                                                                           2 M Y - . 
    1,3,4,5,11                                                                                           A D 5 9 . 
    2,4,5,6,7                                                                                            N 5 9 2 M 
    1,3,5,7,8                                                                                            A D 9 M Y (7 row(s) affected)*/
      

  5. CREATE FUNCTION dbo.getdata(@s1 VARCHAR(50),@s2 VARCHAR(50))
        RETURNS VARCHAR(50)
    AS
    BEGIN
        DECLARE @t TABLE(id int identity,data VARCHAR(10))
        DECLARE @result VARCHAR(50)
        INSERT @t(data)
            SELECT
                SUBSTRING(@s2,number,CHARINDEX(',',@s2+',',number)-number)
            FROM master.dbo.spt_values
            WHERE type='p'
                AND number BETWEEN 1 AND LEN(@s2)
                AND SUBSTRING(','[email protected],number,1)=','
        
        SET @result=''
        SELECT
            @[email protected]+' '+data
        FROM @t
        WHERE CHARINDEX(','+RTRIM(id)+',',','[email protected]+',')>0
        
        RETURN STUFF(@result,1,1,'');
    END
    GOcreate table #tb(f1 varchar(100)) 
    insert into #tb 
    select '5,8,9,10,13' union all 
    select '5,8,10,12,13'union all 
    select '5,9,10,12,13'union all 
    select '6,7,8,9,11'  union all 
    select '1,3,4,5,11'  union all 
    select '2,4,5,6,7'  union all 
    select '1,3,5,7,8' SELECT
        f1,
        dbo.getdata(f1,'A,N,D,5,9,2,M,Y,-,X,.,+,0,4,S')
    FROM #tbGO
    DROP TABLE #tb
    DROP FUNCTION dbo.getdata
      

aliyun

类似问题 »