Wie kann man durch Kommas getrennte Datenspalten in Datenbanken effizient verknüpfen?

Komma-getrennte Datenspalten verbinden: Detaillierte Lösungen

In Datenbanksystemen ist es oft notwendig, in Komma-getrennte Datenspalten zu manipulieren. getrennte Spalten. Die Normalisierung von Daten in mehreren Tabellen ist eine ideale Lösung, es gibt jedoch Fälle, in denen dies möglicherweise nicht möglich ist. Hier stellen wir verschiedene Methoden vor, um durch Kommas getrennte Spalten effizient zu verbinden.

Normalisierung und Tabellenverknüpfungen

Die Normalisierung der Daten in separate Tabellen ist der effizienteste Ansatz. Dazu gehört das Erstellen einer neuen Tabelle mit einer Zeile für jeden eindeutigen Wert in der durch Kommas getrennten Spalte. Die Tabellen können dann mithilfe einer Fremdschlüsselbeziehung verknüpft werden.

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

Nach der Normalisierung können die Daten einfach mithilfe eines Joins abgefragt werden:

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

Benutzerdefinierte Split-Funktion für Nicht normalisierte Daten

Wenn eine Normalisierung nicht möglich ist, können wir eine benutzerdefinierte Aufteilungsfunktion erstellen, um die durch Kommas getrennten Daten zu konvertieren 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;

Mit dieser Funktion können wir die Originaltabelle mit den geteilten Daten verbinden:

;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

FÜR XML PATH Direct Application

Eine andere Methode beinhaltet die direkte Anwendung des FOR XML PATH Feature:

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

Die optimale Lösung hängt vom jeweiligen Szenario ab. Die Normalisierung der Daten ist die effizienteste Option. Wenn dies jedoch nicht möglich ist, kann die Verwendung einer benutzerdefinierten Teilungsfunktion oder die direkte Anwendung von FOR XML PATH effiziente Ergebnisse liefern.

Das obige ist der detaillierte Inhalt vonWie kann man durch Kommas getrennte Datenspalten in Datenbanken effizient verknüpfen?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!