Bagaimana untuk Menyertai Data Dengan Cekap Merentas Lajur Dibatasi Koma dalam SQL?

Menyertai Lajur Data Terhad Koma

Nilai dipisahkan koma (CSV) biasanya digunakan untuk menyimpan berbilang nilai dalam satu lajur dalam pangkalan data hubungan. Walau bagaimanapun, format ini boleh memberikan cabaran apabila melaksanakan tugasan manipulasi data. Artikel ini meneroka teknik untuk menyertai data merentas lajur yang dipisahkan koma.

Contoh Senario

Pertimbangkan dua jadual berikut:

Jadual 1 (T1)

col1	col2
C1	john
C2	alex
C3	piers
C4	sara

Jadual 2 (T2)

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

Output yang Diingini:

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

Penormalan untuk Optimum Prestasi

Sebaik-baiknya, data harus dinormalisasi, menghapuskan nilai yang dipisahkan koma daripada Jadual 2. Dengan mencipta jadual baharu dengan struktur berikut:

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

Data boleh dimasukkan sewajarnya, membolehkan gabungan cekap:

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

Pertanyaan Langsung Menyertai:

Menggunakan jadual biasa, gabungan ringkas boleh mendapatkan semula data yang diingini:

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

Penggabungan untuk Output Dipisahkan Koma:

Jika output yang dikehendaki memerlukan nilai yang dipisahkan koma, FOR XML PATH dan fungsi STUFF boleh digunakan:

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;

Fungsi Pisah untuk Data Tidak Normal:

Jika tiada data ternormal, fungsi pisah boleh dibuat untuk membahagikan nilai yang dipisahkan koma kepada individu baris:

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;

Menggunakan fungsi split dalam CTE (Ungkapan Jadual Biasa), data boleh diproses:

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

Alternatif FOR XML PATH Query:

Satu lagi pendekatan melibatkan aplikasi terus 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;

Kesimpulan

Menyertai merentas data yang dibataskan koma memerlukan pertimbangan yang teliti terhadap prestasi dan pemformatan output. Normalisasi menawarkan prestasi optimum, tetapi jika tidak boleh dilaksanakan, fungsi split atau pertanyaan FOR XML PATH terus menyediakan alternatif. Teknik ini membolehkan manipulasi data yang cekap dan mendapatkan semula daripada lajur yang dipisahkan koma.

Atas ialah kandungan terperinci Bagaimana untuk Menyertai Data Dengan Cekap Merentas Lajur Dibatasi Koma dalam SQL?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!