mysql - sql 子查询连接

Question

我正在尝试允许使用数据库中的几个表自定义创建表和列。

数据在一个名为 CustomData 的表中，

ID | CustomEntries_ID | CustomColumn_ID | Value
-------------------------------------------------
1  |       1          |        1        | Pheven
2  |       1          |        2        | Routine
3  |       1          |        3        | Planned
4  |       1          |        4        | 2014
5  |       2          |        1        | John
6  |       2          |        2        | Routine
7  |       2          |        3        | Planned
8  |       2          |        4        | 2017


SELECT * FROM (
    (SELECT CustomEntries_ID AS ID, `Value` AS `Name` FROM `CustomData` WHERE CustomColumn_ID = 1) AS T1 NATURAL JOIN
    (SELECT CustomEntries_ID AS ID, `Value` AS `Servive` FROM `CustomData` WHERE CustomColumn_ID = 2) AS T2 NATURAL JOIN
    (SELECT CustomEntries_ID AS ID, `Value` AS `Type` FROM `CustomData` WHERE CustomColumn_ID = 3) AS T3 NATURAL JOIN
    (SELECT CustomEntries_ID AS ID, `Value` AS `Model` FROM `CustomData` WHERE CustomColumn_ID = 4) AS T4 NATURAL JOIN
    )

第一个问题似乎是，如果有一个条目缺少 CustomData 表，则与该 customEntries_ID 相关的所有数据都被排除在外。第二个问题是这看起来效率很低，我敢肯定有人对此查询有更好的建议，或者允许创建自定义表而不实际在数据库上创建新表。

Answer 1

只需使用条件聚合:

SELECT customernetyid
       max(case when CustomColumn_ID = 1 then value end) as name,
       max(case when CustomColumn_ID = 2 then value end) as service,
       max(case when CustomColumn_ID = 3 then value end) as type,
       max(case when CustomColumn_ID = 4 then value end) as model
FROM CustomData
GROUP BY customentryid;