mysqli : merge rows in 1 based on some parameters

Question

我有一个 mysql 表，那里有相同的文本，但语言不同

ID, Lang, Text, number, chapter

 1, English, Hello, 2, 1

 4, German, Hallo, 2, 1

 5, Spanish, Hola, 2, 1

怎么才能看到这样的人

Select * from table where chapter=1  (and language: english,german,spanish)

ID, lang1, lang2, lang3, number, chapter

 5, Hello, Hallo, Hola, 2, 1

我想加入 1 行中的列

Answer 1

创建表测试:

 CREATE TABLE test (
                id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                Lang VARCHAR(30) NOT NULL,
                Text VARCHAR(30) NOT NULL,
                number VARCHAR(50),
                chapter INT(10)) 

插入记录:

insert into test values(1,'English','Hello','2',1);
insert into test values(4,'German','Hallo','2',1);     
insert into test values(5,'Spanish','Hola','2',1); 


+----+---------+-------+--------+---------+
| id | Lang    | Text  | number | chapter |
+----+---------+-------+--------+---------+
|  1 | English | Hello | 2      |       1 |
|  4 | German  | Hallo | 2      |       1 |
|  5 | Spanish | Hola  | 2      |       1 |
+----+---------+-------+--------+---------+



SELECT MAX(id) id, MAX(CASE WHEN Lang ='English' THEN Text END) lang1, 
    MAX(CASE WHEN Lang ='German' THEN Text END) lang2, 
    MAX(CASE WHEN Lang ='Spanish' THEN Text END) lang3, number, chapter 
    FROM test GROUP BY number, chapter;

+------+-------+-------+-------+--------+---------+
| id   | lang1 | lang2 | lang3 | number | chapter |
+------+-------+-------+-------+--------+---------+
|    5 | Hello | Hallo | Hola  | 2      |       1 |
+------+-------+-------+-------+--------+---------+