MySQL，多行分隔字段

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

我有一个MySQL表，其中包含以下字段和数据;

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?

我将如何构建视图或select语句来完成此任务？

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.

然而，这为每个字段提供了一个单独的行，我需要一行。

1 个解决方案

#1

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

#1