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

I have a MySQL table with fields and data such as follows;

PartNumber Priority SupName

a1 0 One

a2 0 One

a2 1 Two

a3 0 One

a4 1 Two

a5 2 Three

I am trying to create a view where the parts that have multiple rows are combined into a single row, and into separate fields such as

Ideally This;

PartNumber Sup1 Sup2 Sup3

a1 One NULL NULL

a2 One Two NULL

a3 One NULL NULL

a4 Two NULL NULL

a5 Three NULL NULL

Or I can live with this

PartNumber Sup1 Sup2 Sup3

a1 One NULL NULL

a2 One Two NULL

a3 One NULL NULL

a4 NULL Two NULL

a5 NULL NULL Three

How would I build a view or select statement to accomplish this?

The closest I have come so far is;

SELECT PartNumber,

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

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

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

FROM SupXref

ORDER BY PartNumber

This however gives me a separate row for each of the fields and I need a single line.

解决方案

You're just missing a group by :)

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

Edit:

After playing for a while I think I got the first solution you're looking for. Give it a try :)

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

For the following table:

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

| 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 |

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

Data is turn into this:

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

| PARTNUMBER | SUP1 | SUP2 | SUP3 |

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

| a1 | | | Three |

| a2 | | Two | |

| a3 | | Two | Three |

| a4 | One | | |

| a5 | One | | Three |

| a6 | One | Two | |

| a7 | One | Two | Three |

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

And finally into this:

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

| PARTNUMBER | SUPP1 | SUPP2 | SUPP3 |

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

| a1 | Three | | |

| a2 | Two | | |

| a3 | Two | Three | |

| a4 | One | | |

| a5 | One | Three | |

| a6 | One | Two | |

| a7 | One | Two | Three |

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