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

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

이 튜토리얼의 운영 환경: 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 '用户id',
    subject VARCHAR(20) COMMENT '科目',
    score DOUBLE COMMENT '成绩',
    PRIMARY KEY(id)
)ENGINE = INNODB DEFAULT CHARSET = utf8;

데이터 삽입

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

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

SELECT * FROM tb_score

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

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

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

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

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

SELECT userid,
SUM(IF(`subject`='语文',score,0)) as '语文',
SUM(IF(`subject`='数学',score,0)) as '数学',
SUM(IF(`subject`='英语',score,0)) as '英语',
SUM(IF(`subject`='政治',score,0)) as '政治' 
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,'total') AS userid,
SUM(IF(`subject`='语文',score,0)) AS 语文,
SUM(IF(`subject`='数学',score,0)) AS 数学,
SUM(IF(`subject`='英语',score,0)) AS 英语,
SUM(IF(`subject`='政治',score,0)) AS 政治,
SUM(IF(`subject`='total',score,0)) AS total
FROM(
    SELECT userid,IFNULL(`subject`,'total') 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;

실행 결과로 표시합니다.

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

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

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

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

6 동적, 열 불확실성에 적합

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

실행 결과:

7. : group_concat()

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

실행 결과:

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

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

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

추천 학습:

mysql 비디오 튜토리얼

위 내용은 mysql에서 행을 열로 변환하는 방법의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!