데이터베이스에서 쉼표로 구분된 데이터 열을 효율적으로 조인하는 방법은 무엇입니까?

쉼표로 구분된 데이터 열 결합: 심층 솔루션

데이터베이스 시스템에서는 쉼표로 저장된 데이터를 조작해야 하는 경우가 많습니다. 분리된 열. 데이터를 여러 테이블로 정규화하는 것이 이상적인 솔루션이지만 이것이 가능하지 않은 경우도 있습니다. 여기에서는 쉼표로 구분된 열을 효율적으로 조인하는 다양한 방법을 제시합니다.

정규화 및 테이블 조인

데이터를 별도의 테이블로 정규화하는 것이 가장 효율적인 접근 방식입니다. 여기에는 쉼표로 구분된 열에 각 고유 값에 대한 행이 포함된 새 테이블을 만드는 작업이 포함됩니다. 그런 다음 외래 키 관계를 사용하여 테이블을 조인할 수 있습니다.

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

정규화되면 조인을 사용하여 데이터를 쉽게 쿼리할 수 있습니다.

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

다음에 대한 사용자 정의 분할 기능 정규화되지 않은 데이터

정규화가 불가능할 경우 사용자 정의 분할 함수를 생성하여 쉼표로 구분된 데이터를 개별 행으로 분리합니다.

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;

이 기능을 사용하면 원본 테이블을 분할 데이터와 결합할 수 있습니다.

;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

FOR XML PATH Direct Application

또 다른 방법은 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를 직접 적용하는 것이 효율적인 결과를 제공할 수 있습니다.

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