連接逗號分隔的數(shù)據(jù)列：深入解決方案

在數(shù)據(jù)庫系統(tǒng)中，經(jīng)常需要操作以逗號分隔的數(shù)據(jù)分離的列。將數(shù)據(jù)規(guī)范化到多個表中是一種理想的解決方案，但在某些情況下這可能不可行。在這里，我們介紹了有效連接逗號分隔列的各種方法。

標準化和表連接

將數(shù)據(jù)標準化到單獨的表中是最有效的方法。這涉及創(chuàng)建一個新表，其中為逗號分隔列中的每個唯一值創(chuàng)建一行。然后可以使用外鍵關(guān)系連接這些表。

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

標準化后，可以使用連接輕松查詢數(shù)據(jù)：

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

自定義拆分函數(shù)非標準化數(shù)據(jù)

如果無法標準化，我們可以創(chuàng)建一個自定義分割函數(shù)來轉(zhuǎn)換將逗號分隔的數(shù)據(jù)分成單獨的行。

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;

使用此函數(shù)，我們可以將原始表與拆分數(shù)據(jù)連接起來：

;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 直接應(yīng)用

另一種方法涉及直接應(yīng)用 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;

結(jié)論

最優(yōu)解取決于具體場景。規(guī)范化數(shù)據(jù)是最有效的選擇，但如果這不可行，使用自定義拆分函數(shù)或直接應(yīng)用 FOR XML PATH 可以提供有效的結(jié)果。

以上是如何高效連接數(shù)據(jù)庫中逗號分隔的數(shù)據(jù)列？的詳細內(nèi)容。更多信息請關(guān)注PHP中文網(wǎng)其他相關(guān)文章！