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