使用数组和循环更新MySQL表

Question

我有一个3列的表

pID | key_name   | value  
-----------------------
10 | 'series'   |'Songs'
10 | 'wood'     |'Beech' 
10 | 'language' |'German'
11 | 'series'   |'Songs'
11 | 'wood'     |'Oak'
11 | 'language' |'French'
12 | 'series'   |'Exams'
12 | 'language' |'English'

我需要更新一个表，其中key_names现在是列名，因此

pID | series | wood  | language
-----------------------------
10 ! 'Songs'|'Beech'|'German'
11 | 'Songs'|'Oak'  |'French'
12 | 'Exams'|       |'English'

现在我可以写一些SQL，比如

UPDATE dest-tbl INNER JOIN start-tbl
ON dest-tbl.pID = start-tbl.pID
SET dest-tbl.series = start-tbl.value
WHERE dest-tbl.key_name = 'series'

但是由于有65个不同的key_name值，这意味着必须在该SQL上有65个变量。

我突然想到，最好的方法可能是创建一个key_name值的数组，并循环遍历它，只是我还没有找到如何做到这一点的线索。

有谁能帮我一下吗?使用MariaDB v10.3

MTIA

---

编辑:我想我接近一个答案与SQL下面。我确实需要将其插入到另一个表中，并根据另一个表中字段的值过滤结果。从SELECT到GROUP的代码创建了我需要的输出，但是我现在遇到了JOIN部分

的问题

INSERT INTO results ('series', 'wood', 'language')
SELECT table1.pID, 
MAX(CASE WHEN table1.meta_key = 'series' THEN table1.meta_value END) 'series',
MAX(CASE WHEN table1.meta_key = 'wood' THEN table1.meta_value END) 'wood',
MAX(CASE WHEN table1.meta_key = 'language' THEN table1.meta_value END) 'language'
FROM table1
GROUP BY table1.pID
INNER JOIN ON table2.id = table1.pID
WHERE table2.id.type = 'product';

Answer 1

SELECT 
t1.pID, 
t1.value,
t2.value,
t3.value
FROM Table1 t1
LEFT JOIN Table1 t2 on t2.pID=t1.pID and t2.key_name='wood'
LEFT JOIN Table1 t3 on t3.pID=t1.pID and t3.key_name='language'
WHERE  t1.key_name='series';

输出:

+ -------- + ---------- + ---------- + ---------- +
| 10       | Songs      | Beech      | German     |
| 11       | Songs      | Oak        | French     |
| 12       | Exams      |            | English    |
+ -------- + ---------- + ---------- + ---------- +

DBFIDDLE

Answer 2

正如我在评论中所说，要获得灵活的解决方案，您需要动态sql

CREATE TABLE tab1 (
`pID` INTEGER,
`key_name` VARCHAR(10),
`value` VARCHAR(9)
);
INSERT INTO tab1
(`pID`, `key_name`, `value`)
VALUES
('10', 'series', 'Songs'),
('10', 'wood', 'Beech'),
('10', 'language', 'German'),
('11', 'series', 'Songs'),
('11', 'wood', 'Oak'),
('11', 'language', 'French'),
('12', 'series', 'Exams'),
('12', 'language', 'English');

SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'max(CASE WHEN `key_name` = ''',
`key_name`,
''' THEN `value` ELSe "" END) AS `',

`key_name`, '`'
)
) INTO @sql

FROM `tab1` ;

SET @sql = CONCAT('SELECT `pID`, ',@sql,' from tab1
GROUP BY `pID`');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

<>以前✓✓pID |语言|系列|木——:|:------- |:----- |:----10 |德语|歌曲|山毛榉11 |法语|歌曲|橡树12 |英语|考试✓

db<此处小提琴>

Answer 3

我的解决方案，测试和工作虽然有点慢，如下:-

INSERT INTO results ('series', 'wood', 'language')
SELECT table1.pID, 
MAX(CASE WHEN table1.meta_key = 'series' THEN table1.meta_value END) 'series',
MAX(CASE WHEN table1.meta_key = 'wood' THEN table1.meta_value END) 'wood',
MAX(CASE WHEN table1.meta_key = 'language' THEN table1.meta_value END) 'language'
FROM table1, table2
WHERE table2.ID=table1_pID AND table2.id.type = 'product';
GROUP BY table1.pID