MySQL，多行分隔字段

2022-01-22 00:00:00 pivot sql group-by mysql coalesce

我有一个 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

理想情况下；

PartNumber  Sup1  Sup2  Sup3
a1          One   NULL  NULL
a2          One   Two   NULL
a3          One   NULL  NULL
a4          Two   NULL  NULL
a5          Three NULL  NULL

或者我可以忍受这个

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.

推荐答案

你只是少了一个组 by :)

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

玩了一段时间后，我想我找到了您正在寻找的第一个解决方案.试试看:)

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

对于下表:

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

相关文章