Bagaimana untuk menukar baris kepada lajur dalam mysql

Kaedah baris ke lajur MySQL: 1. Gunakan penukaran operasi "SUM(nama jadual KES APABILA nama medan KEMUDIAN skor ELSE 0 TAMAT) sebagai nama medan" 2. Gunakan "SUM(JIKA(nama jadual = medan nama,skor,0)) sebagai nama medan" penukaran operasi.

Persekitaran pengendalian tutorial ini: sistem Windows 10, versi mysql8.0.22, komputer Dell G3.

Cara menukar baris kepada lajur dalam mysql

Baris ke lajur

Iaitu, kandungan berbeza daripada berbilang baris di bawah lajur yang sama digunakan sebagai berbilang medan dan yang sepadan kandungan adalah output.

Pernyataan penciptaan jadual

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;

Sisipkan data

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

Soal kandungan dalam jadual data (iaitu, hasil sebelum penukaran)

SELECT * FROM tb_score

Mari kita lihat hasil yang ditukar dahulu:

Dapat dilihat bahawa penukaran baris di sini adalah untuk memilih berbilang baris medan subjek asal sebagai set keputusan Lajur yang berbeza dikumpulkan mengikut id pengguna untuk memaparkan skor yang sepadan.

1. Use case...bila....kemudian untuk menukar baris dan lajur

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 baris Lajur:

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

Nota:

(1) SUM() boleh menggunakan GROUP BY kepada kumpulan mengikut userid, kerana setiap userid sepadan dengan subjek= "language "Hanya terdapat satu rekod, jadi nilai SUM() adalah sama dengan nilai skor yang sepadan dengan rekod itu.

Jika terdapat dua rekod dengan userid ='001' dan subject='中文', maka nilai SUM() akan menjadi jumlah kedua-dua rekod ini Begitu juga, gunakan Max() Nilainya nilai terbesar antara dua rekod. Tetapi dalam keadaan biasa, pengguna hanya mempunyai satu skor yang sepadan dengan subjek, jadi fungsi agregat seperti SUM(), MAX(), MIN() dan AVG() boleh digunakan untuk mencapai kesan penukaran baris.

(2) JIKA(`subjek`='中文',skor,0) sebagai syarat, iaitu, operasi SUM(), MAX(), MIN( ), AVG(), jika skor mempunyai tiada nilai, ia lalai kepada 0.

3. Gunakan SUM(IF()) untuk menjana lajur DENGAN ROLLUP untuk menjana baris ringkasan dan gunakan IFNULL untuk memaparkan tajuk baris ringkasan sebagai Jumlah

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;

menjalankan hasil :

4 Gunakan SUM(IF()) untuk menjana lajur UNION untuk menjana baris ringkasan dan gunakan IFNULL untuk memaparkan tajuk baris ringkasan sebagai. Jumlah

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

Jalankan hasil:

5. Gunakan SUM(IF()) untuk menjana lajur dan jana terus hasil tanpa menggunakan subqueries

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;

Hasil operasi:

6. Dinamik, sesuai untuk ketidakpastian lajur

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;

Hasil berjalan:

7 Paparan medan Gabung: Gunakan group_concat()

SELECT userid,GROUP_CONCAT(`subject`,":",score)AS 成绩 FROM tb_score
GROUP BY userid

Hasil yang dijalankan:

group_concat(), manual menyatakan: Fungsi ini mengembalikan hasil rentetan dengan nilai bukan NULL untuk penggabungan daripada kumpulan.

Relatif abstrak dan sukar difahami. Untuk memahaminya dengan cara yang mudah, ia sebenarnya seperti ini: group_concat() akan mengira baris mana yang tergolong dalam kumpulan yang sama dan memaparkan lajur milik kumpulan yang sama. Lajur yang hendak dikembalikan ditentukan oleh parameter fungsi (nama medan). Mesti ada standard untuk kumpulan, iaitu mengumpulkan mengikut lajur yang ditentukan oleh kumpulan mengikut.

Kesimpulan: Fungsi group_concat() boleh menukar berbilang baris kepunyaan kumpulan yang sama kepada satu lajur.

Pembelajaran yang disyorkan: tutorial video mysql

Atas ialah kandungan terperinci Bagaimana untuk menukar baris kepada lajur dalam mysql. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!