我有一个MySQL表,其中包含以下字段和数据;
PartNumber Priority SupName
a1 0 One
a2 0 One
a2 1 Two
a3 0 One
a4 1 Two
a5 2 Three
我正在尝试创建一个视图,其中具有多行的部分组合成一行,并分成单独的字段,如
理想情况下;
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
我将如何构建视图或select语句来实现此目的?
我到目前为止最接近的是;
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
然而,这为每个字段提供了一个单独的行,我需要一行.
解决方法:
你只是错过了一个小组:)
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
编辑:
玩了一段时间后,我想我得到了你想要的第一个解决方案.试试看 :)
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 |
+------------+-------+-------+-------+