none
求一条SQL语句 RRS feed

  • 问题

  • 表A 字段ID(int),ClassId(nvarchar(50))

    表B 字段ID(int),ClassName(nvarchar(50))

    表A的ClassId是一个列表用逗号分割的,例如“1,2,3,4,6,8”,对应的是表B的ID字段

    有没有什么办法用一条语句查询出表A,并且把表A的ClassId字段中的数字替换成表B中对应ID的ClassName

    多谢!


    欢迎到我的博客一起讨论 http://www.raymanzhai.com
    2011年2月16日 10:18

答案

  • 你好,

    我想你首先创建一个表值函数用来分割A表中ClassID 的值,请参阅下面的函数:

    CREATE FUNCTION dbo.Split 
    ( 
        @String VARCHAR(200), 
        @Delimiter VARCHAR(5) 
    ) 
    RETURNS @SplittedValues TABLE 
    ( 
     OccurenceId SMALLINT IDENTITY(1,1), 
     SplitValue VARCHAR(200) 
    ) 
    AS 
    BEGIN 
    DECLARE @SplitLength INT 
    
    WHILE LEN(@String) > 0 
    BEGIN 
        SELECT @SplitLength = (CASE CHARINDEX(@Delimiter,@String) WHEN 0 THEN 
    LEN(@String) ELSE CHARINDEX(@Delimiter,@String) -1 END) 
    
        INSERT INTO @SplittedValues 
        SELECT SUBSTRING(@String,1,@SplitLength) 
    
        SELECT @String = (CASE (LEN(@String) - @SplitLength) WHEN 0 THEN '' 
    ELSE RIGHT(@String, LEN(@String) - @SplitLength - 1) END) 
    END 
    RETURN 
    END
    
    
    

    然后使用此函数,并且与B表进行连接,请参阅下面的示例:

    DECLARE @A TABLE
    (
    ID INT,
    ClassID NVARCHAR(50)
    )
    
    INSERT INTO @A VALUES(1,'1,2,3,4,6,8')
    
    DECLARE @B TABLE
    (
    ID INT,
    ClassName NVARCHAR(50)
    )
    
    INSERT INTO @B VALUES(1,'Test1')
    INSERT INTO @B VALUES(2,'Test2')
    INSERT INTO @B VALUES(3,'Test3')
    INSERT INTO @B VALUES(4,'Test4')
    INSERT INTO @B VALUES(6,'Test6')
    INSERT INTO @B VALUES(8,'Test8')
    
    SELECT SplitValue,B.ClassName FROM @A A CROSS APPLY dbo.Split(A.ClassID,',')
    INNER JOIN @B B ON SplitValue=B.ID
    
    
    


    谢谢,
    邱爱华

     


    Ai-hua Qiu[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    2011年2月17日 8:41

全部回复

  • 你好,

    我想你首先创建一个表值函数用来分割A表中ClassID 的值,请参阅下面的函数:

    CREATE FUNCTION dbo.Split 
    ( 
        @String VARCHAR(200), 
        @Delimiter VARCHAR(5) 
    ) 
    RETURNS @SplittedValues TABLE 
    ( 
     OccurenceId SMALLINT IDENTITY(1,1), 
     SplitValue VARCHAR(200) 
    ) 
    AS 
    BEGIN 
    DECLARE @SplitLength INT 
    
    WHILE LEN(@String) > 0 
    BEGIN 
        SELECT @SplitLength = (CASE CHARINDEX(@Delimiter,@String) WHEN 0 THEN 
    LEN(@String) ELSE CHARINDEX(@Delimiter,@String) -1 END) 
    
        INSERT INTO @SplittedValues 
        SELECT SUBSTRING(@String,1,@SplitLength) 
    
        SELECT @String = (CASE (LEN(@String) - @SplitLength) WHEN 0 THEN '' 
    ELSE RIGHT(@String, LEN(@String) - @SplitLength - 1) END) 
    END 
    RETURN 
    END
    
    
    

    然后使用此函数,并且与B表进行连接,请参阅下面的示例:

    DECLARE @A TABLE
    (
    ID INT,
    ClassID NVARCHAR(50)
    )
    
    INSERT INTO @A VALUES(1,'1,2,3,4,6,8')
    
    DECLARE @B TABLE
    (
    ID INT,
    ClassName NVARCHAR(50)
    )
    
    INSERT INTO @B VALUES(1,'Test1')
    INSERT INTO @B VALUES(2,'Test2')
    INSERT INTO @B VALUES(3,'Test3')
    INSERT INTO @B VALUES(4,'Test4')
    INSERT INTO @B VALUES(6,'Test6')
    INSERT INTO @B VALUES(8,'Test8')
    
    SELECT SplitValue,B.ClassName FROM @A A CROSS APPLY dbo.Split(A.ClassID,',')
    INNER JOIN @B B ON SplitValue=B.ID
    
    
    


    谢谢,
    邱爱华

     


    Ai-hua Qiu[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    2011年2月17日 8:41