如何在 SQL 中高效地跨逗号分隔列连接数据？

连接逗号分隔的数据列

逗号分隔值 (CSV) 通常用于在单个列中存储多个值关系数据库。然而，这种格式在执行数据操作任务时可能会带来挑战。本文探讨了跨逗号分隔列连接数据的技术。

示例场景

考虑以下两个表：

表 1 (T1)

col1	col2
C1	john
C2	alex
C3	piers
C4	sara

表 2 (T2)

col1	col2
R1	C1,C2,C4
R2	C3,C4
R3	C1,C4

期望输出：

col1	col2
R1	john,alex,sara
R2	piers,sara
R3	john,sara

最优归一化性能

理想情况下，数据应该标准化，消除表 2 中的逗号分隔值。通过创建具有以下结构的新表：

CREATE TABLE T2 (
    col1 varchar(2),
    col2 varchar(2),
    PRIMARY KEY (col1, col2),
    FOREIGN KEY (col2) REFERENCES T1 (col1)
);

可以插入数据相应地，实现高效的连接：

INSERT INTO T2 (col1, col2) VALUES ('R1', 'C1'), ('R1', 'C2'), ('R1', 'C4'), ('R2', 'C3'), ('R2', 'C4'), ('R3', 'C1'), ('R3', 'C4');

直接查询连接：

使用规范化表，简单的连接可以检索所需的数据：

SELECT t2.col1, t1.col2
FROM t2
INNER JOIN t1
ON t2.col2 = t1.col1;

连接逗号分隔的输出：

如果所需的输出需要逗号分隔值，则可以使用 FOR XML PATH 和 STUFF 函数使用：

SELECT DISTINCT t2.col1, 
STUFF(
    (SELECT DISTINCT ', ' + t1.col2
    FROM t1
    INNER JOIN t2 t ON t1.col1 = t.col2
    WHERE t2.col1 = t.col1
    FOR XML PATH ('')), 1, 1, '') AS col2
FROM t2;

非标准化数据的分割函数：

在没有标准化数据的情况下，可以创建一个分割函数来将逗号分隔的值分成单独的值rows:

CREATE FUNCTION [dbo].[Split](@String varchar(MAX), @Delimiter char(1))       
RETURNS @temptable TABLE (items varchar(MAX))       
AS       
BEGIN      
    DECLARE @idx int       
    DECLARE @slice varchar(8000)       

    SELECT @idx = 1       
    IF len(@String)<1 OR @String IS NULL RETURN       

    WHILE @idx != 0       
    BEGIN       
        SET @idx = CHARINDEX(@Delimiter, @String)       
        IF @idx != 0       
            SET @slice = LEFT(@String, @idx - 1)       
        ELSE       
            SET @slice = @String       

        IF(LEN(@slice) > 0)  
            INSERT INTO @temptable(Items) VALUES(@slice)       

        SET @String = RIGHT(@String, LEN(@String) - @idx)       
        IF LEN(@String) = 0 BREAK       
    END   
RETURN 
END;

在 CTE（通用表表达式）中使用 split 函数，数据可以处理：

WITH CTE AS
(
  SELECT c.col1, t1.col2
  FROM t1
  INNER JOIN 
  (
    SELECT t2.col1, i.items AS col2
    FROM t2
    CROSS APPLY dbo.Split(t2.col2, ',') i
  ) c
    ON t1.col1 = c.col2
) 
SELECT DISTINCT c.col1, 
STUFF(
    (SELECT DISTINCT ', ' + c1.col2
    FROM CTE c1
    WHERE c.col1 = c1.col1
    FOR XML PATH('')), 1, 1, '') AS col2
FROM CTE c

替代 FOR XML PATH 查询：

另一种方法涉及直接应用 FOR XML PATH：

SELECT col1, 
(
  SELECT ', '+t1.col2
  FROM t1
  WHERE ','+t2.col2+',' LIKE '%,'+CAST(t1.col1 AS VARCHAR(10))+',%'
  FOR XML PATH(''), TYPE
).value('SUBSTRING(TEXT()[1], 3)', 'VARCHAR(MAX)') AS col2
FROM t2;

结论

跨界加入逗号分隔的数据需要仔细考虑性能和输出格式。规范化可提供最佳性能，但如果不可行，则拆分函数或直接 FOR XML PATH 查询可提供替代方案。这些技术可以从逗号分隔的列中进行高效的数据操作和检索。

以上是如何在 SQL 中高效地跨逗号分隔列连接数据？的详细内容。更多信息请关注PHP中文网其他相关文章！