SQL에서 쉼표로 구분된 열 전체에 걸쳐 데이터를 효율적으로 조인하는 방법은 무엇입니까?

쉼표로 구분된 데이터 열 결합

쉼표로 구분된 값(CSV)은 일반적으로 단일 열 내에 여러 값을 저장하는 데 사용됩니다. 관계형 데이터베이스. 그러나 이 형식은 데이터 조작 작업을 수행할 때 문제가 될 수 있습니다. 이 문서에서는 쉼표로 구분된 열 전체에서 데이터를 조인하는 기술을 살펴봅니다.

예시 시나리오

다음 두 테이블을 고려하세요.

표 1 (T1)

col1	col2
C1	john
C2	alex
C3	piers
C4	sara

표 2 (T2)

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

원하는 출력:

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

최적을 위한 정규화 성능

이상적으로는 표 2에서 쉼표로 구분된 값을 제거하여 데이터를 정규화해야 합니다. 다음 구조로 새 테이블을 생성하면

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

데이터를 삽입할 수 있습니다. 그에 따라 효율적인 조인 활성화:

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

직접 쿼리 조인:

정규화된 테이블을 사용하면 간단한 조인으로 원하는 데이터를 검색할 수 있습니다.

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

쉼표로 구분된 출력 연결:

원하는 출력에 쉼표로 구분된 값이 필요한 경우 FOR XML PATH 및 STUFF 함수를 사용할 수 있습니다. 채용됨:

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;

정규화되지 않은 데이터에 대한 분할 함수:

정규화된 데이터가 없는 경우 분할 함수를 생성하여 쉼표로 구분된 값을 개별 항목으로 나눌 수 있습니다. 행:

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;

CTE(공통 테이블 표현식) 내에서 분할 기능을 사용하면 데이터를 다음과 같이 만들 수 있습니다. 처리됩니다:

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

대체 FOR XML PATH 쿼리:

또 다른 접근 방식은 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 쿼리가 대안을 제공합니다. 이러한 기술을 사용하면 쉼표로 구분된 열에서 효율적인 데이터 조작 및 검색이 가능합니다.

위 내용은 SQL에서 쉼표로 구분된 열 전체에 걸쳐 데이터를 효율적으로 조인하는 방법은 무엇입니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!