如何在 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

最優歸一化性能

最優歸一化性能

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');

理想情況下，資料應該標準化，消除表2 中的逗號分隔值。透過建立具有以下結構的新表：

可以插入資料相應地，實現高效的連接：

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

直接查詢連接：

使用規範化表，簡單的連接可以檢索所需的資料：

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;

連接逗號分隔的輸出：

如果所需的輸出需要逗號分隔值，則可以使用 FOR XML PATH 和 STUFF函數使用：

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;

非標準化資料的分割函數：

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

在沒有標準化資料的情況下，可以建立分割函數來將逗號分隔的值分成單獨的值rows:

在CTE（通用表表達式）中使用split函數，資料可以處理：

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 查詢：

另一種方法涉及直接應用FOR XML PATH：

結論跨界加入逗號分隔的資料需要仔細考慮效能和輸出格式。規範化可提供最佳效能，但如果不可行，則拆分函數或直接 FOR XML PATH 查詢可提供替代方案。這些技術可以從逗號分隔的欄位中進行高效率的資料操作和檢索。

以上是如何在 SQL 中有效率地跨逗號分隔列連接資料？的詳細內容。更多資訊請關注PHP中文網其他相關文章！