mysql 查询1对n,MySQL数据透视表1对n的关系

I have data like this

table master_item

+-------+---------+

|item_id|item_name|

+-------+---------+

| 001 | Car A |

| 002 | Car B |

+-------+---------+

and relation into table process

+-------+--------+

|proc_id|proc_seq|

+-------+--------+

| 001 | 1 |

| 001 | 2 |

| 001 | 3 |

| 001 | 4 |

| 001 | 5 |

| 002 | 1 |

| 002 | 2 |

| 002 | 3 |

+-------+--------+

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 ?

解决方案

The problem lies in PIVOT TABLE category.

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;

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.

EDIT:

In order to get result for each 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;