So konvertieren Sie Zeilen in Spalten in MySQL

Mysql-Zeilen-zu-Spalten-Methode: 1. Verwenden Sie die Operationskonvertierung „SUM(CASE Tabellenname WHEN Feldname THEN Score ELSE 0 END)“ 2. Verwenden Sie „SUM(IF(Tabellenname = Feldname, Score, 0)) als Feldname“-Operationskonvertierung.

Die Betriebsumgebung dieses Tutorials: Windows10-System, MySQL8.0.22-Version, Dell G3-Computer.

So konvertieren Sie Zeilen in Spalten in MySQL

Zeile in Spalte

Das heißt, der unterschiedliche Inhalt mehrerer Zeilen unter derselben Spalte wird als mehrere Felder verwendet und der entsprechende Inhalt ausgegeben.

Tabellenanweisung erstellen

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;

Daten einfügen

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

Fragen Sie den Inhalt der Datentabelle ab (d. h. das Ergebnis vor der Konvertierung)

SELECT * FROM tb_score

Werfen wir einen Blick auf das Ergebnis nach der Konvertierung:

Das kann sein Ich habe gesehen, dass sich die Zeile hier ändert. Die Spalte besteht darin, mehrere Zeilen des ursprünglichen Betrefffelds als unterschiedliche Spalten im Ergebnissatz auszuwählen und sie entsprechend der Benutzer-ID zu gruppieren, um die entsprechende Punktzahl anzuzeigen.

1. Anwendungsfall...wann...dann um Zeilen in Spalten umzuwandeln

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. Verwenden Sie IF(), um Zeilen in Spalten umzuwandeln:

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

Hinweise:

(1) SUM () besteht darin, GROUP BY zum Gruppieren nach Benutzer-ID verwenden zu können, da jeder Benutzer-ID nur ein Datensatz mit subject="Sprache" entspricht, sodass der Wert von SUM() gleich dem Bewertungswert des entsprechenden Datensatzes ist .

Wenn es zwei Datensätze mit Benutzer-ID = „001“ und Betreff = „中文“ gibt, ist der Wert von SUM() die Summe dieser beiden Datensätze. Ebenso ist der Wert bei der Verwendung von Max() der mit der größte Wert unter diesen beiden Datensätzen. Unter normalen Umständen verfügt ein Benutzer jedoch nur über eine Punktzahl, die einem Thema entspricht, sodass Aggregatfunktionen wie SUM(), MAX(), MIN() und AVG() verwendet werden können, um den Effekt der Zeilenkonvertierung zu erzielen.

(2) IF(`subject`='中文',score,0) als Bedingung, d. h. SUM(), MAX(), MIN(), AVG( )-Operation, wenn der Score keinen Wert hat, wird er ausgeführt Der Standardwert ist 0.

3. Verwenden Sie SUM(IF()), um Spalten zu generieren + WITH ROLLUP, um Zusammenfassungszeilen zu generieren, und verwenden Sie IFNULL, um den Zusammenfassungszeilentitel als Total anzuzeigen

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;

Laufergebnisse:

4 SUM(IF ()) Generieren Sie Spalten + UNION, um Zusammenfassungszeilen zu generieren, und verwenden Sie IFNULL, um den Titel der Zusammenfassungszeile als Gesamt anzuzeigen

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

Laufende Ergebnisse:

5. Verwenden Sie SUM(IF()) zum Generieren Spalten und die direkt generierten Ergebnisse werden nicht wiederverwendet Feldanzeige : Verwenden Sie group_concat()

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;

Run-Ergebnisse:

group_concat(), im Handbuch heißt es: Diese Funktion gibt ein String-Ergebnis mit Nicht-NULL-Werten aus der Verbindung einer Gruppe zurück. Relativ abstrakt und schwer zu verstehen. Um es auf einfache Weise zu verstehen, sieht es tatsächlich so aus: group_concat() berechnet, welche Zeilen zur gleichen Gruppe gehören und zeigt die Spalten an, die zur gleichen Gruppe gehören. Welche Spalten zurückgegeben werden sollen, wird durch die Funktionsparameter (Feldnamen) bestimmt. Es muss einen Standard für die Gruppierung geben, der darin besteht, nach der durch „group by“ angegebenen Spalte zu gruppieren.

Fazit: Die Funktion group_concat() kann mehrere Zeilen, die zur gleichen Gruppe gehören, in eine Spalte umwandeln.

Empfohlenes Lernen: MySQL-Video-Tutorial

Das obige ist der detaillierte Inhalt vonSo konvertieren Sie Zeilen in Spalten in MySQL. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!