gpt4 book ai didi

MySQL,多行分隔字段

转载 作者:可可西里 更新时间:2023-11-01 07:05:35 26 4
gpt4 key购买 nike

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

PartNumber  Priority SupName
a1 0 One
a2 0 One
a2 1 Two
a3 0 One
a4 1 Two
a5 2 Three

我正在尝试创建一个 View ,其中将多行的部分组合成一行,并组合成单独的字段,例如

理想情况下是这样;

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

我将如何构建 View 或选择语句来完成此操作?

到目前为止我最接近的是;

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

关于MySQL,多行分隔字段,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/9356374/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com