MySQL，多行来分隔字段 [英] MySQL, multiple rows to separate fields

问题描述

我有一个带有字段和数据的MySQL表，如下所示：

  PartNumber优先级名称
 a1 0一
 a2 0 One 
 a2 1 Two 
 a3 0 One 
 a4 1 Two 
 a5 2 Three 
  

我试图创建一个视图，其中有多行的部分组合成单行，并分成不同的字段，如

理想情况下这样;

  PartNumber Sup1 Sup2 Sup3 
 a1 One NULL NULL 
 a2一个两个NULL 
 a3一个NULL NULL 
 a4两个NULL NULL 
 a5三个NULL NULL 
  

或者我可以接受这个

  PartNumber Sup1 Sup2 Sup3 
 a1一个NULL NULL 
 a2一个两个NULL 
 a3一个NULL NULL 
 a4 NULL两个NULL 
 a5 NULL NULL三
  

我将如何构建一个视图或select语句来完成此操作？

距离我最近的是;

  SELECT PartNumber，
 IF（优先级= 0，SupName，NULL）AS Sup1，
 IF（Priority = 1，SupName，NULL）AS Sup2，
 IF（Priority = 2，SupName，NULL）AS Sup3 
 FROM SupXref 
 ORDER BY PartNumber 
  

然而这给了我一个单独的行并且我需要一行代码。

解决方案

  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 
  
  
 
 
 编辑：
 
 
认为我得到了f你正在寻找的第一个解决方案。试试看：）
 
 pre $  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， （IF（Priority = 0，SupName，NULL））AS Sup1，
 MAX（IF（Priority = NULL，Sup3））AS Supp3 
 FROM（
 SELECT PartNumber，
 MAX 1，SupName，NULL））AS AS Sup2，
 MAX（IF（Priority = 2，SupName，NULL））AS Sup3 
 FROM SupXref 
 GROUP BY PartNumber 
）AS S 
  

对于下表：

  + ------------ + ---------- + --------- + 
 | PARTNUMBER |优先| SUPNAME | 
 + ------------ + ---------- + --------- + 
 | a1 | 2 |三| 
 | a2 | 1 |两个| 
 | a3 | 2 |三| 
 | a3 | 1 |两个| 
 | a4 | 0 | One | 
 | a5 | 0 | One | 
 | a5 | 2 |三| 
 | a6 | 0 | One | 
 | a6 | 1 |两个| 
 | a7 | 0 | One | 
 | a7 | 1 |两个| 
 | a7 | 2 |三| 
 + ------------ + ---------- + --------- + 
  
  
 $ b $ p 
 $ b $ p $  +  - ----------- + ------ + ------ + ------- + 
 | PARTNUMBER | SUP1 | SUP2 | SUP3 | 
 + ------------ + ------ + ------ + ------- + 
 | a1 | | |三| 
 | a2 | |两个| | 
 | a3 | |两个|三| 
 | a4 | One | | | 
 | a5 | One | |三| 
 | a6 | One |两个| | 
 | a7 | One |两个|三| 
 + ------------ + ------ + ------ + ------- + 
  
 
 
 
最后进入此处： 
 
  +  - ----------- + ------- + ------- + ------- + 
 | PARTNUMBER | SUPP1 | SUPP2 | SUPP3 | 
 + ------------ + ------- + ------- + ------- + 
 | a1 |三| | | 
 | a2 |两个| | | 
 | a3 |两个|三| | 
 | a4 | One | | | 
 | a5 | One |三| | 
 | a6 | One |两个| | 
 | a7 | One |两个|三| 
 + ------------ + ------- + ------- + ------- + 
  
 
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 |
+------------+-------+-------+-------+


                        
这篇关于MySQL，多行来分隔字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！