データベース内のカンマ区切りのデータ列を効率的に結合するにはどうすればよいですか?

カンマ区切りデータ列の結合: 詳細なソリューション

データベース システムでは、多くの場合、カンマ区切りで保存されたデータを操作する必要があります。分離された列。データを複数のテーブルに正規化することは理想的な解決策ですが、それが実現できない場合もあります。ここでは、カンマ区切りの列を効率的に結合するためのさまざまな方法を紹介します。

正規化とテーブル結合

データを個別のテーブルに正規化するのが最も効率的な方法です。これには、カンマ区切りの列に一意の値ごとに行を含む新しいテーブルを作成することが含まれます。その後、外部キー関係を使用してテーブルを結合できます。

-- 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 Direct Application

もう 1 つの方法では、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 中国語 Web サイトの他の関連記事を参照してください。