mysql에서 행을 열로 변환하는 방법-MySQL 튜토리얼-php.cn

mysql에서 행을 열로 변환하는 방법

WBOY

풀어 주다： 2022-03-28 15:44:27

원래의

13212명이 탐색했습니다.

Mysql 행 대 열 방법: 1. "SUM(CASE 테이블 이름 WHEN 필드 이름 THEN 점수 ELSE 0 END)을 필드 이름으로 사용" 작업 변환을 사용합니다. 2. "SUM(IF(테이블 이름 = 필드 이름, 점수, 0)) 필드 이름으로" 작업 변환.

mysql에서 행을 열로 변환하는 방법

이 튜토리얼의 운영 환경: windows10 시스템, mysql8.0.22 버전, Dell G3 컴퓨터.

mysql에서 행을 열로 변환하는 방법

행을 열로

즉, 동일한 열 아래 여러 행의 서로 다른 내용을 여러 필드로 사용하여 해당 내용을 출력하는 것입니다.

테이블 문 만들기

DROP TABLE IF EXISTS tb_score;
CREATE TABLE tb_score(
    id INT(11) NOT NULL auto_increment,
    userid VARCHAR(20) NOT NULL COMMENT &#39;用户id&#39;,
    subject VARCHAR(20) COMMENT &#39;科目&#39;,
    score DOUBLE COMMENT &#39;成绩&#39;,
    PRIMARY KEY(id)
)ENGINE = INNODB DEFAULT CHARSET = utf8;

로그인 후 복사

데이터 삽입

INSERT INTO tb_score(userid,subject,score) VALUES (&#39;001&#39;,&#39;语文&#39;,90);
INSERT INTO tb_score(userid,subject,score) VALUES (&#39;001&#39;,&#39;数学&#39;,92);
INSERT INTO tb_score(userid,subject,score) VALUES (&#39;001&#39;,&#39;英语&#39;,80);
INSERT INTO tb_score(userid,subject,score) VALUES (&#39;002&#39;,&#39;语文&#39;,88);
INSERT INTO tb_score(userid,subject,score) VALUES (&#39;002&#39;,&#39;数学&#39;,90);
INSERT INTO tb_score(userid,subject,score) VALUES (&#39;002&#39;,&#39;英语&#39;,75.5);
INSERT INTO tb_score(userid,subject,score) VALUES (&#39;003&#39;,&#39;语文&#39;,70);
INSERT INTO tb_score(userid,subject,score) VALUES (&#39;003&#39;,&#39;数学&#39;,85);
INSERT INTO tb_score(userid,subject,score) VALUES (&#39;003&#39;,&#39;英语&#39;,90);
INSERT INTO tb_score(userid,subject,score) VALUES (&#39;003&#39;,&#39;政治&#39;,82);

로그인 후 복사

데이터 테이블의 내용(즉, 변환 전 결과) 쿼리

SELECT * FROM tb_score

로그인 후 복사

변환 후 결과를 살펴보겠습니다.

mysql에서 행을 열로 변환하는 방법

다음과 같이 할 수 있습니다. 여기서 행이 변경되는 것을 볼 수 있습니다. 열은 원래 주제 필드의 여러 행을 결과 집합의 다른 열로 선택하고 사용자 ID에 따라 그룹화하여 해당 점수를 표시하는 것입니다.

1. 행을 열로 변환하려면...when....then을 사용하세요.

SELECT userid,
SUM(CASE `subject` WHEN &#39;语文&#39; THEN score ELSE 0 END) as &#39;语文&#39;,
SUM(CASE `subject` WHEN &#39;数学&#39; THEN score ELSE 0 END) as &#39;数学&#39;,
SUM(CASE `subject` WHEN &#39;英语&#39; THEN score ELSE 0 END) as &#39;英语&#39;,
SUM(CASE `subject` WHEN &#39;政治&#39; THEN score ELSE 0 END) as &#39;政治&#39; 
FROM tb_score 
GROUP BY userid

로그인 후 복사

2. IF()를 사용하여 행을 열로 변환합니다.

SELECT userid,
SUM(IF(`subject`=&#39;语文&#39;,score,0)) as &#39;语文&#39;,
SUM(IF(`subject`=&#39;数学&#39;,score,0)) as &#39;数学&#39;,
SUM(IF(`subject`=&#39;英语&#39;,score,0)) as &#39;英语&#39;,
SUM(IF(`subject`=&#39;政治&#39;,score,0)) as &#39;政治&#39; 
FROM tb_score 
GROUP BY userid

로그인 후 복사

참고:

(1) SUM ( )는 GROUP BY를 사용하여 사용자 ID별로 그룹화할 수 있도록 하는 것인데, 이는 각 사용자 ID에 해당하는 subject="언어"의 레코드가 하나만 있기 때문에 SUM()의 값은 해당 레코드의 점수 값과 동일하기 때문입니다. .

userid ='001' 및 subject='中文'인 두 개의 레코드가 있는 경우 SUM()의 값은 이 두 레코드의 합계가 됩니다. 마찬가지로 Max()를 사용하는 값은 다음과 같습니다. 두 레코드 중 가장 큰 값입니다. 그러나 일반적인 상황에서 사용자는 주제에 해당하는 점수가 하나만 있으므로 SUM(), MAX(), MIN() 및 AVG()와 같은 집계 함수를 사용하여 행 변환 효과를 얻을 수 있습니다.

(2) IF(`subject`='中文',score,0)을 조건으로, 즉 SUM(), MAX(), MIN(), AVG( ) 연산으로 점수에 값이 없으면 기본값은 0입니다.

3 SUM(IF())을 사용하여 열을 생성하고 WITH ROLLUP을 사용하여 요약 행 제목을 Total

SELECT IFNULL(userid,&#39;total&#39;) AS userid,
SUM(IF(`subject`=&#39;语文&#39;,score,0)) AS 语文,
SUM(IF(`subject`=&#39;数学&#39;,score,0)) AS 数学,
SUM(IF(`subject`=&#39;英语&#39;,score,0)) AS 英语,
SUM(IF(`subject`=&#39;政治&#39;,score,0)) AS 政治,
SUM(IF(`subject`=&#39;total&#39;,score,0)) AS total
FROM(
    SELECT userid,IFNULL(`subject`,&#39;total&#39;) AS `subject`,SUM(score) AS score
    FROM tb_score
    GROUP BY userid,`subject`
    WITH ROLLUP
    HAVING userid IS NOT NULL
)AS A 
GROUP BY userid
WITH ROLLUP;

로그인 후 복사

실행 결과로 표시합니다.

mysql에서 행을 열로 변환하는 방법

4. SUM(IF ()) 열 + UNION을 생성하여 요약 행을 생성하고 IFNULL을 사용하여 요약 행 제목을 Total

SELECT userid,
SUM(IF(`subject`=&#39;语文&#39;,score,0)) AS 语文,
SUM(IF(`subject`=&#39;数学&#39;,score,0)) AS 数学,
SUM(IF(`subject`=&#39;英语&#39;,score,0)) AS 英语,
SUM(IF(`subject`=&#39;政治&#39;,score,0)) AS 政治,
SUM(score) AS TOTAL 
FROM tb_score
GROUP BY userid
UNION
SELECT &#39;TOTAL&#39;,SUM(IF(`subject`=&#39;语文&#39;,score,0)) AS 语文,
SUM(IF(`subject`=&#39;数学&#39;,score,0)) AS 数学,
SUM(IF(`subject`=&#39;英语&#39;,score,0)) AS 英语,
SUM(IF(`subject`=&#39;政治&#39;,score,0)) AS 政治,
SUM(score) FROM tb_score

로그인 후 복사

로 표시합니다. 실행 결과:

mysql에서 행을 열로 변환하는 방법

5 SUM(IF())을 사용하여 생성합니다. 열이며 직접 생성된 결과는 재사용 하위 쿼리가 아닙니다.

mysql에서 행을 열로 변환하는 방법 6 동적, 열 불확실성에 적합

SELECT IFNULL(userid,&#39;TOTAL&#39;) AS userid,
SUM(IF(`subject`=&#39;语文&#39;,score,0)) AS 语文,
SUM(IF(`subject`=&#39;数学&#39;,score,0)) AS 数学,
SUM(IF(`subject`=&#39;英语&#39;,score,0)) AS 英语,
SUM(IF(`subject`=&#39;政治&#39;,score,0)) AS 政治,
SUM(score) AS TOTAL 
FROM tb_score
GROUP BY userid WITH ROLLUP;

로그인 후 복사

실행 결과:

mysql에서 행을 열로 변환하는 방법 7. : group_concat()

SET @EE=&#39;&#39;;
select @EE :=CONCAT(@EE,&#39;sum(if(subject= \&#39;&#39;,subject,&#39;\&#39;,score,0)) as &#39;,subject, &#39;,&#39;) AS aa FROM (SELECT DISTINCT subject FROM tb_score) A ;
SET @QQ = CONCAT(&#39;select ifnull(userid,\&#39;TOTAL\&#39;)as userid,&#39;,@EE,&#39; sum(score) as TOTAL from tb_score group by userid WITH ROLLUP&#39;);
-- SELECT @QQ;
PREPARE stmt FROM @QQ;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

로그인 후 복사

실행 결과:

group_concat(), 매뉴얼 상태: 이 함수는 그룹의 연결에 대해 NULL이 아닌 값이 포함된 문자열 결과를 반환합니다. mysql에서 행을 열로 변환하는 방법

상대적으로 추상적이고 이해하기 어렵습니다. 간단히 이해하면 실제로는 다음과 같습니다. group_concat()은 어떤 행이 동일한 그룹에 속하는지 계산하고 동일한 그룹에 속하는 열을 표시합니다. 반환할 열은 함수 매개변수(필드 이름)에 따라 결정됩니다. 그룹핑 기준이 있어야 합니다. 즉, 그룹화 기준으로 지정한 열에 따라 그룹화하는 것입니다.

결론: group_concat() 함수는 동일한 그룹에 속한 여러 행을 하나의 열로 변환할 수 있습니다.