从 WP_PostMeta 行到列的 MySQL 查询

Question

我正在尝试从 wordpress 表 wp_postmeta 进行查询我需要下图中从 _form_id 到 _sub_id 的数据。我正在使用这个 sql

SELECT meta_value,
MAX(CASE WHEN meta_key = '_field_14' THEN meta_value END) as a1,
MAX(CASE WHEN meta_key = '_field_15' THEN meta_value END) as a2,
MAX(CASE WHEN meta_key = '_field_17' THEN meta_value END) as a3,
MAX(CASE WHEN meta_key = '_field_18' THEN meta_value END) as a4
FROM wp_postmeta
Group by post_id

但a1 到a4 上的NULL 数据仍然出现在结果中。我的SQL Fiddel请指教，谢谢

预期结果

Answer 1

您需要选择 post_id，group by 中不存在 meta_value，因此您将从每个帖子中获得一个随机值

也只有帖子 54,56, 57 有这些字段，因此其余帖子将获得 NULL 值。

SELECT post_id,
                MAX(CASE WHEN meta_key = '_field_14' THEN meta_value  END) as a1,
                MAX(CASE WHEN meta_key = '_field_15' THEN meta_value  END) as a2,
                MAX(CASE WHEN meta_key = '_field_17' THEN meta_value  END) as a3,
                MAX(CASE WHEN meta_key = '_field_18' THEN meta_value  END) as a4
                FROM wp_postmeta
                Group by post_id

编辑:

根据结果，您只需要所有具有值的帖子的元值，您可以使用子查询和合并来获得您想要的内容

SELECT * FROM
(
SELECT 
       MAX(CASE WHEN meta_key = '_field_14' THEN meta_value END) as a1,
       MAX(CASE WHEN meta_key = '_field_15' THEN meta_value END) as a2,
       MAX(CASE WHEN meta_key = '_field_17' THEN meta_value END) as a3,
       MAX(CASE WHEN meta_key = '_field_18' THEN meta_value END) as a4
FROM wp_postmeta
Group by post_id
)T
where coalesce(a1,a2,a3,a4) is not null