mysql - 使用 Case When 将 1 列拆分为多列同时忽略空值

Question

我是 SQL 的新手，尝试了很多搜索但无法完成我想要的。

我有一个包含在一个表和一列中的自定义字段值，每个自定义字段都有自己的 ID，但存储的值在一列中。

我正在尝试为每个自定义字段获取 1 列及其关联值，这是我的查询:

SELECT PRODUCT.id AS ProductId, PRODUCT.num AS ProductNumber, 
CASE WHEN customvarcharlong.customfieldid = '6' THEN customvarcharlong.info 
END AS OEM1Name, 
CASE WHEN customvarcharlong.customfieldid = '7' THEN customvarcharlong.info 
END AS OEM1NumbersA
FROM product
JOIN customvarcharlong ON product.id=customvarcharlong.recordid
JOIN customfield ON customvarcharlong.customfieldid=customfield.id
WHERE PRODUCT.activeFlag = TRUE
ORDER BY PRODUCT.id

我的问题是，我为每个具有值的自定义字段获取一行，而 case 语句只是为每个不匹配 customfieldid 的字段返回 NULL。

2   00007   NULL    NULL
2   00007   NULL    NULL
2   00007   NULL    NULL
2   00007   NULL    NULL
2   00007   NULL    NULL
2   00007   Comp1   NULL
2   00007   NULL    1234
2   00007   NULL    NULL
2   00007   NULL    NULL

这是我想要的结果:

2   00007   Comp1   1234

使用 where 或 group by 消除了一些数据，因为它们位于不同的行上。这可能吗？我有 48 个自定义字段要添加，因此您可以想象，正确添加会节省大量工作。

谢谢!

编辑以添加来自 customvarcharlong 的样本数据:

+--------+---------------+-------+----------+
|   id   | customFieldId | info  | recordId |
+--------+---------------+-------+----------+
| 170887 |           171 | N     |     1481 |
| 170888 |           172 | 3.03  |     1481 |
| 170889 |           173 | new   |     1481 |
| 170890 |           170 | 0     |     1481 |
| 170891 |             5 | 0     |     1481 |
| 170892 |             6 | OEM1  |     1481 |
| 170893 |             7 | Num1  |     1481 |
| 170894 |           200 | 4184  |     1481 |
| 170895 |           120 | Truck |     1481 |
+--------+---------------+-------+----------+

您可能会说问题是 recordid 重复存在的自定义字段值，并且 customfieldid 在每个 recordid 上重复。我想获取每个 recordid (recordid=productid) 所在列中每一行的信息值。

Answer 1

您正在使用 EAV数据模型。本质上，您需要为每个属性 (customfieldid) 单独左连接到values 表(customvarcharlong)想要返回一组给定的实体(产品)。

这样试试:

SELECT PRODUCT.id AS ProductId, 
       PRODUCT.num AS ProductNumber, 
       cvcl6.info AS OEM1Name, 
       cvcl7.info AS OEM1NumbersA
FROM product
    LEFT JOIN customvarcharlong cvcl6
        ON product.id=cvcl6.recordid
            AND cvcl6.customfieldid = '6'
    LEFT JOIN customvarcharlong cvcl7
        ON product.id=cvcl7.recordid
            AND cvcl7.customfieldid = '7'
WHERE PRODUCT.activeFlag = TRUE
ORDER BY PRODUCT.id