Comment joindre efficacement des colonnes de données séparées par des virgules dans des bases de données ?

Joindre une colonne de données séparées par des virgules : solutions approfondies

Dans les systèmes de bases de données, il est souvent nécessaire de manipuler des données stockées entre virgules. colonnes séparées. La normalisation des données dans plusieurs tables est une solution idéale, mais il existe des cas où cela peut ne pas être réalisable. Nous présentons ici différentes méthodes pour joindre efficacement des colonnes délimitées par des virgules.

Normalisation et jointures de tables

La normalisation des données dans des tables séparées est l'approche la plus efficace. Cela implique de créer un nouveau tableau avec une ligne pour chaque valeur unique dans la colonne séparée par des virgules. Les tables peuvent ensuite être jointes à l'aide d'une relation de clé étrangère.

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

Une fois normalisées, les données peuvent être facilement interrogées à l'aide d'une jointure :

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

Fonction de fractionnement personnalisée pour Données non normalisées

Si la normalisation n'est pas possible, nous pouvons créer une fonction de fractionnement personnalisée pour convertir les données séparées par des virgules en lignes individuelles.

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;

En utilisant cette fonction, nous pouvons joindre la table d'origine avec les données fractionnées :

;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

POUR XML PATH Direct Application

Une autre méthode implique l’application directe du FOR XML PATH fonctionnalité :

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;

Conclusion

La solution optimale dépend du scénario spécifique. La normalisation des données est l'option la plus efficace, mais si cela n'est pas réalisable, l'utilisation d'une fonction de fractionnement personnalisée ou l'application directe de FOR XML PATH peut fournir des résultats efficaces.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!