MySql - 数据透视表

fudge 发表于 Dev

做傻事

我试图从 wp_postmeta 表中提取一些数据，该表基本上是一系列与数字 post_id 相关的键/值对。因此，当我尝试为帖子提取各种值时，这就是我得到的：

这是我用来获取该输出的当前查询：

select post_id,meta_key,meta_value from wp_postmeta
where meta_key in ('_sku','_length','_width','_height')
and post_id in (
    select post_id from wp_postmeta
    where meta_value in ('28-005080','28-005287')
    )
    order by post_id DESC

我想要做的是格式化这样的信息：

我试图查看 MySql 数据透视表示例，但我不确定它们是否非常适合这个特定场景。坦率地说，我不知道从哪里开始完成这项任务。

专卖店

您可以使用条件聚合：

select post_id,
    max(case when meta_key = '_sku'    then meta_value end) as sku,
    max(case when meta_key = '_length' then meta_value end) as length,
    max(case when meta_key = '_width'  then meta_value end) as width,
    max(case when meta_key = '_height' then meta_value end) as height
from wp_postmeta
where 
    meta_key in ('_sku','_length','_width','_height')
    and post_id in (select post_id from wp_postmeta where meta_value in ('28-005080','28-005287'))
group by post_id
order by post_id desc

实际上，我们也许可以where用having子句替换子句中的子查询：

select post_id,
    max(case when meta_key = '_sku'    then meta_value end) as sku,
    max(case when meta_key = '_length' then meta_value end) as length,
    max(case when meta_key = '_width'  then meta_value end) as width,
    max(case when meta_key = '_height' then meta_value end) as height
from wp_postmeta
group by post_id
having max(meta_value in ('28-005080','28-005287')) = 1
order by post_id desc

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。