sql - 在 SQL Server 中将具有逗号分隔 varchar 值的表与另一个表的 Id 值连接起来

Question

我有两个表，我们将它们称为 Users 和 Fruit。

用户

ID   Name    Fruit
-------------------
1    Bob      1,3
2    Jack     3

水果

ID   Name
-------------
 1   Apple
 2   Orange
 3   Grape

如何连接这两个表以用用户选择的水果名称填充数据表？

我需要编写一个带循环的存储过程吗？

我对 SQL Server 相当陌生，很高兴获得任何帮助或指出正确的方向。

Answer 1

使用示例数据创建物理表

CREATE TABLE TempUsers 
( ID INT,
  Name  VARCHAR(100),
  Fruit VARCHAR(100)
 )
INSERT INTO TempUsers 
SELECT 1,'Bob' ,'1,3' UNION ALL
SELECT 2,'Jack','3'

CREATE TABLE TempFruit 
    ( ID INT,
      Name  VARCHAR(100))

INSERT INTO TempFruit
SELECT 1,'Apple' UNION ALL
SELECT 2,'Orange'UNION ALL
SELECT 3,'Grape'

创建一个表值函数来检索以逗号分隔的水果名称

CREATE FUNCTION [dbo].[udf_GetFruitNames]
(
@vc_String nvarchar(max)
)
RETURNS  @OutTable TABLE
(
Reqdata nvarchar(max)
)
AS
BEGIN

    DECLARE @Temp AS TABLE
    (
        DATA nvarchar(max)
    )
    INSERT INTO @Temp
    SELECT @vc_String;

    DECLARE @Temp1 AS TABLE
    (
    DATA nvarchar(max)
    )
    INSERT INTO @Temp1
    SELECT 
            STUFF((SELECT DISTINCT ','+ Name FROM
            (
                SELECT  ID,
                        Name    
                FROm TempFruit 
                WHERE ID IN  ( SELECT    
                                    CAST(Split.a.value('.', 'nvarchar(1000)') AS INT) AS FruitId
                                    FROM
                                    (   SELECT    
                                        CAST( '<S>'+ REPLACE(DATA,',','</S><S>')+'</S>' AS XML) AS FruitId             
                                        FROM @Temp f

                                    )AS A
                                    CROSS APPLY FruitId.nodes('S') AS Split(a))
            ) As dt FOR XML PATH ('')),1,1,'') As FruitName


        INSERT INTO @OutTable
        SELECT * FROM @Temp1
RETURN
END

Sql查询

SELECT ID
      ,Name
      ,uf.Reqdata AS FruitNames
FROM TempUsers u
CROSS APPLY [dbo].[udf_GetFruitNames](u.Fruit) AS uf

Or

SELECT ID
  ,Name
  ,(SELECT Reqdata FROM [dbo].[udf_GetFruitNames](u.Fruit) ) AS FruitNames
  FROM TempUsers u

结果

ID  Name    FruitNames
---------------------
1   Bob     Apple,Grape
2   Jack    Grape