Mysql Pivot 查询建议

SELECT u.user_id,
  MAX(CASE d.type WHEN 'type_a' THEN COALESCE(c.value, d.value) END) AS type_a,
  MAX(CASE d.type WHEN 'type_b' THEN COALESCE(c.value, d.value) END) AS type_b,
  MAX(CASE d.type WHEN 'type_c' THEN COALESCE(c.value, d.value) END) AS type_c,
  MAX(CASE d.type WHEN 'type_d' THEN COALESCE(c.value, d.value) END) AS type_d,
  MAX(CASE d.type WHEN 'type_e' THEN COALESCE(c.value, d.value) END) AS type_e
FROM user AS u
CROSS JOIN Default_Properties AS d 
LEFT OUTER JOIN Custom_Properties AS c ON u.user_id=c.fk_user_id AND c.type=d.type
GROUP BY u.user_id
ORDER BY u.user_id;

在MySQL 8.0.29上测试的输出:

+---------+--------+--------+--------+--------+--------+
| user_id | type_a | type_b | type_c | type_d | type_e |
+---------+--------+--------+--------+--------+--------+
|    1001 | val_a  | val_b  | val_c  | val_d  | val_e  |
|    1002 | val_a  | banana | val_c  | val_d  | val_e  |
|    1003 | val_a  | val_b  | val_c  | val_d  | val_e  |
|    1004 | val_a  | val_b  | val_c  | val_d  | val_e  |
|    1005 | val_a  | val_b  | val_c  | durian | val_e  |
+---------+--------+--------+--------+--------+--------+

在SQL中不可能实现"动态列".

在分析查询时，即在读取表中的任何数据之前，列必须是固定的.因此，除了您在编写查询时在SELECT-LIST中显式指定的列之外，查询在读取数据时无法展开列.

以此类推，这就像是调用一个Java函数，其名称是该函数的返回值.

因此，要创建有效的透视表，如果要为其创建列的属性可能会更改，则必须按两个步骤执行查询:首先，知道要为其创建列的属性集.类似以下内容将为您提供属性列表:

SELECT DISTINCT type FROM Default_Properties
UNION
SELECT DISTINCT type FROM Custom_Properties;

然后使用该查询的结果构建您的数据透视表查询.(即代码循环，将表达式作为字符串附加到查询的 Select 列表中.)