mysql解析分隔字段_MySQL,多行来分隔字段

你只是错过了一个小组:)

SELECT PartNumber,

MAX(IF (Priority = 0, SupName, NULL)) AS Sup1,

MAX(IF (Priority = 1, SupName, NULL)) AS Sup2,

MAX(IF (Priority = 2, SupName, NULL)) AS Sup3

FROM SupXref

GROUP BY PartNumber

编辑：

玩了一段时间后,我想我得到了你想要的第一个解决方案.试试看 ：)

SELECT partnumber,

COALESCE(Sup1, COALESCE(Sup2, Sup3)) AS Supp1,

IF (Sup1 IS NULL, IF (Sup2 IS NULL, NULL, Sup3), COALESCE(Sup2, Sup3)) AS Supp2,

IF (Sup1 IS NULL, NULL, IF (Sup2 IS NULL, NULL, Sup3)) AS Supp3

FROM (

SELECT PartNumber,

MAX(IF (Priority = 0, SupName, NULL)) AS Sup1,

MAX(IF (Priority = 1, SupName, NULL)) AS Sup2,

MAX(IF (Priority = 2, SupName, NULL)) AS Sup3

FROM SupXref

GROUP BY PartNumber

) AS S

对于下表：

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

| PARTNUMBER | PRIORITY | SUPNAME |

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

| a1 | 2 | Three |

| a2 | 1 | Two |

| a3 | 2 | Three |

| a3 | 1 | Two |

| a4 | 0 | One |

| a5 | 0 | One |

| a5 | 2 | Three |

| a6 | 0 | One |

| a6 | 1 | Two |

| a7 | 0 | One |

| a7 | 1 | Two |

| a7 | 2 | Three |

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

数据转变为：

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

| PARTNUMBER | SUP1 | SUP2 | SUP3 |

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

| a1 | | | Three |

| a2 | | Two | |

| a3 | | Two | Three |

| a4 | One | | |

| a5 | One | | Three |

| a6 | One | Two | |

| a7 | One | Two | Three |

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

最后进入这个：

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

| PARTNUMBER | SUPP1 | SUPP2 | SUPP3 |

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

| a1 | Three | | |

| a2 | Two | | |

| a3 | Two | Three | |

| a4 | One | | |

| a5 | One | Three | |

| a6 | One | Two | |

| a7 | One | Two | Three |

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