MySQL数据透视表1对n的关系 [英] MySQL pivot table in 1 to n relationship

问题描述

我有这样的数据 表master_item

I have data like this table master_item

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

并与表进程建立联系

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

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

and i want result like this when i select item_id = 001

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

产生该结果的查询是什么?

what is the query to produce this result ?

推荐答案

问题出在PIVOT TABLE类别.

在这里，您可以通过以下查询完成结果:

Here you can accomplish your result through the query given below:

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;

工作演示

但是就像我说的那样，最好在应用程序逻辑中完成这种工作.否则，仅通过MySQL来完成它会很麻烦.

But like I said, better do this kind of job in application logic. Otherwise it will be too cumbersome to make it done through MySQL alone.

为了获得每个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;

此处显示 kbd>

这篇关于MySQL数据透视表1对n的关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！