如何有效率地連接資料庫中逗號分隔的資料列？

連接逗號分隔的資料列：深入解決方案

在資料庫系統中，經常需要操作以逗號分隔的資料分離的列。將資料標準化到多個表中是一種理想的解決方案，但在某些情況下這可能不可行。在這裡，我們介紹了有效連接逗號分隔列的各種方法。

標準化和表格連接

將資料標準化到單獨的表格中是最有效的方法。這涉及建立一個新表，其中為逗號分隔列中的每個唯一值建立一行。然後可以使用外鍵關係連接這些表。

-- T1 Table
CREATE TABLE T1
(
  col1 varchar(2), 
  col2 varchar(5),
  constraint pk1_t1 primary key (col1)
);

-- T2 Table
CREATE TABLE T2
(
  col1 varchar(2), 
  col2 varchar(2),
  constraint pk1_t2 primary key (col1, col2),
  constraint fk1_col2 foreign key (col2) references t1 (col1)
);

標準化後，可以使用連接輕鬆查詢資料：

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

自訂分割函數非標準化資料

如果無法自訂分割函數非標準化資料

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 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, '') col2
FROM cte c

使用此函數，我們可以將原始表與分割資料連接：

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 直接套用

FOR XML PATH 直接套用

FOR XML PATH 直接套用另一種方法涉及直接應用FOR XML PATH特徵：結論最優解取決於特定場景。規範化資料是最有效的選擇，但如果這不可行，使用自訂分割函數或直接應用 FOR XML PATH 可以提供有效的結果。

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