Wie verbinde ich Daten effizient über durch Kommas getrennte Spalten in SQL?

Komma-getrennte Datenspalten verbinden

Komma-getrennte Werte (CSV) werden häufig verwendet, um mehrere Werte innerhalb einer einzelnen Spalte in einem zu speichern relationale Datenbank. Dieses Format kann jedoch bei der Durchführung von Datenmanipulationsaufgaben zu Herausforderungen führen. In diesem Artikel werden Techniken zum Verbinden von Daten über durch Kommas getrennte Spalten untersucht.

Beispielszenario

Betrachten Sie die folgenden zwei Tabellen:

Tabelle 1 (T1)

col1	col2
C1	john
C2	alex
C3	piers
C4	sara

Tabelle 2 (T2)

col1	col2
R1	C1,C2,C4
R2	C3,C4
R3	C1,C4

Gewünschte Ausgabe:

col1	col2
R1	john,alex,sara
R2	piers,sara
R3	john,sara

Normalisierung für optimale Leistung

Ideal , sollten die Daten normalisiert werden, wodurch durch Kommas getrennte Werte aus Tabelle 2 entfernt werden. Durch Erstellen einer neuen Tabelle mit Folgendem Struktur:

CREATE TABLE T2 (
    col1 varchar(2),
    col2 varchar(2),
    PRIMARY KEY (col1, col2),
    FOREIGN KEY (col2) REFERENCES T1 (col1)
);

Daten können entsprechend eingefügt werden, was effiziente Verknüpfungen ermöglicht:

INSERT INTO T2 (col1, col2) VALUES ('R1', 'C1'), ('R1', 'C2'), ('R1', 'C4'), ('R2', 'C3'), ('R2', 'C4'), ('R3', 'C1'), ('R3', 'C4');

Direkte Abfrageverknüpfung:

Verwendung der normalisierten Tabellen , ein einfacher Join kann die gewünschten Daten abrufen:

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

Verkettung für Durch Kommas getrennte Ausgabe:

Wenn die gewünschte Ausgabe durch Kommas getrennte Werte erfordert, können die Funktionen FOR XML PATH und STUFF verwendet werden:

SELECT DISTINCT t2.col1, 
STUFF(
    (SELECT DISTINCT ', ' + t1.col2
    FROM t1
    INNER JOIN t2 t ON t1.col1 = t.col2
    WHERE t2.col1 = t.col1
    FOR XML PATH ('')), 1, 1, '') AS col2
FROM t2;

Split-Funktion für nicht normalisiert Daten:

In Ermangelung normalisierter Daten kann eine Teilungsfunktion zum Teilen erstellt werden Durch Kommas getrennte Werte in einzelne Zeilen:

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;

Mithilfe der Split-Funktion innerhalb eines CTE (Common Table Expression) können die Daten verarbeitet werden:

WITH CTE AS
(
  SELECT c.col1, t1.col2
  FROM t1
  INNER JOIN 
  (
    SELECT t2.col1, i.items AS 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, '') AS col2
FROM CTE c

Alternative FOR XML-PATH-Abfrage:

Ein anderer Ansatz beinhaltet die direkte Anwendung von FOR XML PFAD:

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;

Fazit

Das Verknüpfen von durch Kommas getrennten Daten erfordert eine sorgfältige Prüfung der Leistung und Ausgabeformatierung. Die Normalisierung bietet optimale Leistung, aber wenn dies nicht möglich ist, bieten Split-Funktionen oder direkte FOR XML PATH-Abfragen Alternativen. Diese Techniken ermöglichen eine effiziente Datenbearbeitung und den Abruf aus durch Kommas getrennten Spalten.

Das obige ist der detaillierte Inhalt vonWie verbinde ich Daten effizient über durch Kommas getrennte Spalten in SQL?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!