programing

MySQL에서 피벗 테이블 출력을 반환하려면 어떻게 해야 합니까?

randomtip 2022. 10. 2. 11:21
반응형

MySQL에서 피벗 테이블 출력을 반환하려면 어떻게 해야 합니까?

MySQL 테이블이 다음과 같은 경우:

company_name 액션 페이지 수-------------------------------A사 프린트 3A사 프린트 2A사 프린트 3B사 이메일B사 PRINT 2B사 PRINT 2B사 프린트 1A사 프린트 3

MySQL 쿼리를 실행하여 다음과 같은 출력을 얻을 수 있습니까?

company_name E-메일 PRINT 1페이지 인쇄 2페이지 인쇄 2페이지-------------------------------------------------------------A사 0 0 0 1 3기업 B 1 1 2 0

그 생각은 이다pagecount의 양은 할 수 각의 됩니다.action/pagecount, 그 후 pair, pair, , .company_name이게 피벗 테이블이라고 하는지 모르겠지만누군가 제안해 주셨어요

이것은 기본적으로 피벗 테이블입니다.

이것을 실현하는 방법에 관한 훌륭한 튜토리얼은, http://www.artfulsoftware.com/infotree/qrytip.php?id=78 를 참조해 주세요.

이 투고를 읽고, 이 솔루션을 고객의 요구에 맞추어 조정하는 것을 추천합니다.

갱신하다

위의 링크를 현재 사용할 수 없게 된 후, 여기서 mysql 피벗 답변을 찾고 있는 여러분 모두에게 몇 가지 추가 정보를 제공해야 할 것 같습니다.방대한 양의 정보가 포함되어 있기 때문에 여기에 있는 모든 것을 포함하지는 않지만(그들의 방대한 지식을 복사하고 싶지 않기 때문에), 처음에 질문을 한 peku의 예에서 일반적인 sql 방식으로 피벗 테이블을 처리하는 방법에 대해 조언합니다.

곧 링크가 돌아올지도 모르니까 잘 지켜보겠습니다.

스프레드시트 방식...

많은 사람들이 MSExcel, OpenOffice 또는 기타 스프레드시트 도구와 같은 도구를 이 용도로 사용합니다.이것은 유효한 해결책입니다.그냥 데이터를 복사하고 GUI가 제공하는 툴을 사용하여 문제를 해결합니다.

하지만... 이건 문제가 아니었고, 데이터를 스프레드시트로 가져오는 방법, 확장 문제 등과 같은 몇 가지 단점이 생길 수도 있습니다.

SQL 방식...

그의 테이블이 이렇게 생겼을 때:

CREATE TABLE `test_pivot` (
  `pid` bigint(20) NOT NULL AUTO_INCREMENT,
  `company_name` varchar(32) DEFAULT NULL,
  `action` varchar(16) DEFAULT NULL,
  `pagecount` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`pid`)
) ENGINE=MyISAM;

이제 원하는 표를 살펴봅니다.

company_name    EMAIL   PRINT 1 pages   PRINT 2 pages   PRINT 3 pages
-------------------------------------------------------------
CompanyA        0       0               1               3
CompanyB        1       1               2               0

EMAIL,PRINT x pages과 유사합니다을 사용하다주요 그룹은 다음과 같습니다.company_name.

조건을 설정하기 위해서는 -스테이트먼트를 사용해야 한다고 외칩니다.무엇인가를 기준으로 그룹화하려면 , 음, ...을 사용합니다. GROUP BY.

이 피벗을 제공하는 기본 SQL은 다음과 같습니다.

SELECT  P.`company_name`,
    COUNT(
        CASE 
            WHEN P.`action`='EMAIL' 
            THEN 1 
            ELSE NULL 
        END
    ) AS 'EMAIL',
    COUNT(
        CASE 
            WHEN P.`action`='PRINT' AND P.`pagecount` = '1' 
            THEN P.`pagecount` 
            ELSE NULL 
        END
    ) AS 'PRINT 1 pages',
    COUNT(
        CASE 
            WHEN P.`action`='PRINT' AND P.`pagecount` = '2' 
            THEN P.`pagecount` 
            ELSE NULL 
        END
    ) AS 'PRINT 2 pages',
    COUNT(
        CASE 
            WHEN P.`action`='PRINT' AND P.`pagecount` = '3' 
            THEN P.`pagecount` 
            ELSE NULL 
        END
    ) AS 'PRINT 3 pages'
FROM    test_pivot P
GROUP BY P.`company_name`;

이렇게 하면 원하는 결과를 매우 빠르게 얻을 수 있습니다.이 접근방식의 주요 단점은 피벗테이블에 행이 많을수록 SQL 문에 정의해야 할 조건이 많아진다는 것입니다.

이것 또한 대처할 수 있기 때문에, 사람들은 준비된 진술서, 루틴, 카운터 등을 사용하는 경향이 있다.

이 항목에 대한 추가 링크:

솔루션은 피벗 없이 T-SQL에 있습니다.

SELECT
    CompanyName,  
    SUM(CASE WHEN (action='EMAIL') THEN 1 ELSE 0 END) AS Email,
    SUM(CASE WHEN (action='PRINT' AND pagecount=1) THEN 1 ELSE 0 END) AS Print1Pages,
    SUM(CASE WHEN (action='PRINT' AND pagecount=2) THEN 1 ELSE 0 END) AS Print2Pages,
    SUM(CASE WHEN (action='PRINT' AND pagecount=3) THEN 1 ELSE 0 END) AS Print3Pages
FROM 
    Company
GROUP BY 
    CompanyName

의 경우 MySQL에 직접 수 .SUM()함수는 Boolean으로 평가됩니다.따라서 사용하지 않고 기준에 따라 카운트를 얻을 수 있습니다.IF/CASE(START)

SELECT
    company_name,  
    SUM(action = 'EMAIL')AS Email,
    SUM(action = 'PRINT' AND pagecount = 1)AS Print1Pages,
    SUM(action = 'PRINT' AND pagecount = 2)AS Print2Pages,
    SUM(action = 'PRINT' AND pagecount = 3)AS Print3Pages
FROM t
GROUP BY company_name

DEMO

피벗의 는, 「」를 합니다.GROUP_CONCATCONCATGROUP_CONCAT 함수는 그룹의 문자열을 다양한 옵션을 가진 하나의 문자열로 연결합니다.

SET @sql = NULL;
SELECT
    GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(CASE WHEN action = "',
      action,'"  AND ', 
           (CASE WHEN pagecount IS NOT NULL 
           THEN CONCAT("pagecount = ",pagecount) 
           ELSE pagecount IS NULL END),
      ' THEN 1 ELSE 0 end) AS ',
      action, IFNULL(pagecount,'')

    )
  )
INTO @sql
FROM
  t;

SET @sql = CONCAT('SELECT company_name, ', @sql, ' 
                  FROM t 
                   GROUP BY company_name');

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

데모 (여기서

부울 로직을 사용하는 stardard-SQL 버전:

SELECT company_name
     , COUNT(action = 'EMAIL' OR NULL) AS "Email"
     , COUNT(action = 'PRINT' AND pagecount = 1 OR NULL) AS "Print 1 pages"
     , COUNT(action = 'PRINT' AND pagecount = 2 OR NULL) AS "Print 2 pages"
     , COUNT(action = 'PRINT' AND pagecount = 3 OR NULL) AS "Print 3 pages"
FROM   tbl
GROUP  BY company_name;

db <>여기에 추가
올드 스클피들

어떻게?

TRUE OR NULL 율율TRUE.
FALSE OR NULL 율율NULL.
NULL OR NULL 율율NULL.
null이 아닌 값만 카운트합니다.보이라

정답은 다음과 같습니다.

select table_record_id,
group_concat(if(value_name='note', value_text, NULL)) as note
,group_concat(if(value_name='hire_date', value_text, NULL)) as hire_date
,group_concat(if(value_name='termination_date', value_text, NULL)) as termination_date
,group_concat(if(value_name='department', value_text, NULL)) as department
,group_concat(if(value_name='reporting_to', value_text, NULL)) as reporting_to
,group_concat(if(value_name='shift_start_time', value_text, NULL)) as shift_start_time
,group_concat(if(value_name='shift_end_time', value_text, NULL)) as shift_end_time
from other_value
where table_name = 'employee'
and is_active = 'y'
and is_deleted = 'n'
GROUP BY table_record_id

MySQL 피벗 테이블 생성기라는 도구가 있으며 나중에 Excel로 내보낼 수 있는 웹 기반 피벗 테이블을 만드는 데 도움이 될 수 있습니다.데이터가 단일 테이블 또는 여러 테이블에 있는 경우 사용할 수 있습니다.

), 값 및 를 .MySQL 피벗 테이블

이 툴의 홈페이지는 https://mysqlreports.com/mysql-reporting-tools/mysql-pivot-table/ 입니다.

select t3.name, sum(t3.prod_A) as Prod_A, sum(t3.prod_B) as Prod_B, sum(t3.prod_C) as    Prod_C, sum(t3.prod_D) as Prod_D, sum(t3.prod_E) as Prod_E  
from
(select t2.name as name, 
case when t2.prodid = 1 then t2.counts
else 0 end  prod_A, 

case when t2.prodid = 2 then t2.counts
else 0 end prod_B,

case when t2.prodid = 3 then t2.counts
else 0 end prod_C,

case when t2.prodid = 4 then t2.counts
else 0 end prod_D, 

case when t2.prodid = "5" then t2.counts
else 0 end prod_E

from 
(SELECT partners.name as name, sales.products_id as prodid, count(products.name) as counts
FROM test.sales left outer join test.partners on sales.partners_id = partners.id
left outer join test.products on sales.products_id = products.id 
where sales.partners_id = partners.id and sales.products_id = products.id group by partners.name, prodid) t2) t3

group by t3.name ;

하나의 옵션은 MySQL Database의 집약 내에서 statement is redundant를 사용하는 것과 다음 코드 블록과 같이 결과 세트의 적절한 열 제목을 얻는 것과 함께 필요한 쿼리 생성을 동적으로 고려하는 것입니다.

SET @sql = NULL;

SELECT GROUP_CONCAT(
             CONCAT('SUM( `action` = ''', action, '''',pc0,' ) AS ',action,pc1)
       )
  INTO @sql
  FROM 
  ( 
   SELECT DISTINCT `action`, 
          IF(`pagecount` IS NULL,'',CONCAT('page',`pagecount`)) AS pc1,
          IF(`pagecount` IS NULL,'',CONCAT(' AND `pagecount` = ', pagecount, '')) AS pc0
     FROM `tab` 
    ORDER BY CONCAT(action,pc0) 
  ) t;

SET @sql = CONCAT('SELECT company_name,',@sql,' FROM `tab` GROUP BY company_name'); 
SELECT @sql; 

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

Demo

SELECT company_name, SUM(CASE WHEN ACTION = 'Email' THEN 1 ELSE 0 END) AS "Email",
SUM(CASE WHEN ACTION = 'Print' AND pagecount = 1 THEN 1 ELSE 0 END) AS "print 1 PAGE",
SUM(CASE WHEN ACTION = 'Print' AND pagecount = 2 THEN 1 ELSE 0 END) AS "print 2 PAGE",
SUM(CASE WHEN ACTION = 'Print' AND pagecount = 3 THEN 1 ELSE 0 END) AS "print 2 PAGE"
FROM test1 GROUP BY company_name;

언급URL : https://stackoverflow.com/questions/7674786/how-can-i-return-pivot-table-output-in-mysql

반응형
저작자표시

'programing' 카테고리의 다른 글

vue.js / Vue getter not defined 오류  (0) 2022.10.02
JavaScript 또는 jQuery를 사용하여 새 탭에서 URL을 여는 방법  (0) 2022.10.02
typescript - 복제 개체  (0) 2022.10.02
자바어로 "캐노컬 형식" 또는 "캐노컬 표현"이라는 용어는 무엇을 의미합니까?  (0) 2022.10.02
표의 모든 열을 나열하려면 어떻게 해야 합니까?  (0) 2022.10.02

'programing'의 다른글

  • 현재글MySQL에서 피벗 테이블 출력을 반환하려면 어떻게 해야 합니까?

관련글

댓글

티스토리툴바