MySQL에서 행 간 피벗을 위한 열을 동적으로 생성하는 방법은 무엇입니까?

MySQL 행-열 동적 열 생성

세 개의 MySQL 테이블이 있다고 가정합니다.

• 제품 테이블: 은 제품 정보(ID, 이름)를 저장합니다.
• 파트너 테이블: 은 파트너 정보(ID, 이름)를 저장합니다.
• 판매 테이블: 판매 거래(partners_id, products_id)를 기록합니다.

우리의 목표는 판매 데이터를 파트너가 행으로, 제품이 동적 열로 나열된 테이블로 변환하는 것입니다.

사전 집계 결과

간단한 쿼리를 사용하면 파트너 이름과 제품 이름이 열로 포함된 테이블과 발생 횟수를 얻을 수 있습니다.

SELECT partners.name AS partner_name, 
       products.name AS product_name, 
       COUNT(*) 
FROM sales
JOIN products ON sales.products_id = products.id
JOIN partners ON sales.partners_id = partners.id
GROUP BY sales.partners_id, sales.products_id

그러나 이 접근 방식에는 가변적인 제품 수량에 대한 동적 열 생성이 부족합니다.

CASE 및 집계 함수를 사용한 동적 피벗

MySQL에는 전용 PIVOT 함수가 없으므로 집계 쿼리에는 CASE 문을 사용해야 합니다.

SELECT pt.partner_name,
  COUNT(CASE WHEN pd.product_name = 'Product A' THEN 1 END) AS ProductA,
  COUNT(CASE WHEN pd.product_name = 'Product B' THEN 1 END) AS ProductB,
  COUNT(CASE WHEN pd.product_name = 'Product C' THEN 1 END) AS ProductC,
  COUNT(CASE WHEN pd.product_name = 'Product D' THEN 1 END) AS ProductD,
  COUNT(CASE WHEN pd.product_name = 'Product E' THEN 1 END) AS ProductE
FROM partners pt
LEFT JOIN sales s ON pt.part_id = s.partner_id
LEFT JOIN products pd ON s.product_id = pd.prod_id
GROUP BY pt.partner_name

이 방법을 사용하면 각 파트너의 각 제품 이름 발생 횟수를 동적으로 계산할 수 있습니다.

Prepared문을 활용한 동적 관점

진정한 동적 피벗 테이블의 경우 준비된 문을 사용하여 제품 수를 기반으로 SQL 쿼리 문자열을 생성할 수 있습니다.

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'COUNT(CASE WHEN Product_Name = ''',
      Product_Name,
      ''' THEN 1 END) AS ',
      REPLACE(Product_Name, ' ', '')
    )
  ) INTO @sql
FROM products;

SET @sql = CONCAT('SELECT pt.partner_name, ', @sql, ' FROM partners pt
LEFT JOIN sales s ON pt.part_id = s.partner_id
LEFT JOIN products pd ON s.product_id = pd.prod_id
GROUP BY pt.partner_name');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

이 방법을 사용하면 각 제품의 열 이름을 동적으로 생성하고 개수를 집계할 수 있습니다. 필요한 경우 GROUP_CONCAT의 크기 제한을 조정하는 것을 잊지 마세요.

위 내용은 MySQL에서 행 간 피벗을 위한 열을 동적으로 생성하는 방법은 무엇입니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!