如何高效连接数据库中逗号分隔的数据列？-mysql教程-PHP中文网

如何高效连接数据库中逗号分隔的数据列？

Susan Sarandon

发布： 2024-12-27 16:07:10

原创

455 人浏览过

How to Efficiently Join Comma-Separated Data Columns in Databases?

连接逗号分隔的数据列：深入解决方案

在数据库系统中，经常需要操作以逗号分隔的数据分离的列。将数据规范化到多个表中是一种理想的解决方案，但在某些情况下这可能不可行。在这里，我们介绍了有效连接逗号分隔列的各种方法。

标准化和表连接

将数据标准化到单独的表中是最有效的方法。这涉及创建一个新表，其中为逗号分隔列中的每个唯一值创建一行。然后可以使用外键关系连接这些表。

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

另一种方法涉及直接应用 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 可以提供有效的结果。

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