1 到 n 关系中的 MySQL 数据透视表

我有这样的数据
表 master_item

+-------+-------+
|item_id|item_name|
+-------+-------+
| 001 |车A |
| 002 |车B |
+-------+-------+

与表进程的关系

+-------+------+
|proc_id|proc_seq|
+-------+------+
| 001 | 1 |
| 001 | 2 |
| 001 | 3 |
| 001 | 4 |
| 001 | 5 |
| 002 | 1 |
| 002 | 2 |
| 002 | 3 |
+-------+------+

当我选择 item_id = 001 时，我想要这样的结果

+-------+---------+-----+-----+-----+-----+-----+- ----+-----+
|item_id|item_name|proc1|proc2|proc3|proc4|proc5|proc6|proc7|
+-------+---------+-----+-----+-----+-----+-----+- ----+-----+
| 001 |车A | 1 | 2 | 3 | 4 | 5 | | |
+-------+---------+-----+-----+-----+-----+-----+- ----+-----+

产生这个结果的查询是什么？

最佳答案

问题在于 PIVOT TABLE 类别。

在这里，您可以通过下面给出的查询来完成您的结果:

SET @sql := '';
SELECT
CONCAT('SELECT
MI.item_id,
MI.item_name,',
GROUP_CONCAT('MAX(CASE WHEN P.proc_seq =', P.proc_seq ,' THEN P.proc_seq END) AS ', 'proc',P.proc_seq,' '),
'FROM master_item MI
INNER JOIN process P ON MI.item_id = P.proc_id
WHERE MI.item_id = 1
GROUP BY P.proc_id') INTO @sql
FROM master_item MI
INNER JOIN process P ON MI.item_id = P.proc_id
WHERE item_id = 1
GROUP BY P.proc_id;


PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE stmt;

WORKING DEMO

但是就像我说的，最好在应用程序逻辑中做这种工作。否则单独通过MySQL来完成会太麻烦。

编辑:

为了获得每个 item_id 的结果

SET @sql := '';
SELECT
CONCAT('SELECT
MI.item_id,
MI.item_name,',
GROUP_CONCAT('MAX(CASE WHEN P.proc_seq =', P.proc_seq ,' THEN P.proc_seq END) AS ', 'proc',P.proc_seq,' '),
'FROM master_item MI
INNER JOIN process P ON MI.item_id = P.proc_id
GROUP BY P.proc_id') INTO @sql
FROM master_item MI
INNER JOIN process P ON MI.item_id = P.proc_id

WHERE
    item_id = (
        SELECT
            maxProcId.proc_id
        FROM
            (
                SELECT
                    proc_id,
                    COUNT(proc_seq) total
                FROM process
                GROUP BY proc_id
                ORDER BY total DESC
                LIMIT 1
            ) AS maxProcId
    )
GROUP BY
    P.proc_id;


PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE stmt;

Demonstrated here

关于1 到 n 关系中的 MySQL 数据透视表，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/38950814/