SQL左连接metas表时将多行动态属性转单行列的实现问题

有两种常用的实现方式，都可以在单SQL内完成需求： #

方案1：多LEFT JOIN关联（最直观易维护） #

每个需要查询的元数据字段单独关联一次metas表，适合待查询的元字段数量较少的场景：

SELECT 
  u.id,
  u.name,
  u.email,
  CAST(JSON_UNQUOTE(m1.value) AS DATE) AS created_at,
  CAST(JSON_UNQUOTE(m2.value) AS DATE) AS logged_at
FROM users u
LEFT JOIN metas m1 ON u.id = m1.user_id AND m1.name = 'created_at'
LEFT JOIN metas m2 ON u.id = m2.user_id AND m2.name = 'logged_at'
-- 支持直接对元字段加筛选条件
-- WHERE CAST(JSON_UNQUOTE(m1.value) AS DATE) > '2020-12-31'
-- 支持直接对元字段排序
ORDER BY created_at DESC, logged_at DESC;

方案2：条件聚合（性能更优，适合多字段查询） #

仅关联一次metas表，通过分组+条件聚合的方式把多行元数据转成多列，待查询元字段越多性能优势越明显：

SELECT 
  u.id,
  u.name,
  u.email,
  MAX(CASE WHEN m.name = 'created_at' THEN CAST(JSON_UNQUOTE(m.value) AS DATE) END) AS created_at,
  MAX(CASE WHEN m.name = 'logged_at' THEN CAST(JSON_UNQUOTE(m.value) AS DATE) END) AS logged_at
FROM users u
LEFT JOIN metas m ON u.id = m.user_id
GROUP BY u.id, u.name, u.email
-- 支持对元字段加筛选（聚合后筛选需要用HAVING）
-- HAVING created_at > '2020-12-31'
ORDER BY created_at DESC, logged_at DESC;

说明 #

如果后续需要新增查询的元字段，只需要对应新增JOIN逻辑（方案1）或者新增CASE WHEN分支（方案2）即可，不需要修改表结构，完全匹配动态属性的业务场景。

内容的提问来源于stack exchange，提问作者Ifnot