sql查询结果合并的问题

图片描述

图片描述

如何将meta_key 为10086和12580的结果查询合并成一条

clipboard.png
我尝试了这种,但结果还是分开(group by 之后结果会丢失)

问题补充:我想查询出来的结果如下

clipboard.png

阅读 3.6k
3 个回答

从所提供的信息看,如果只是为了单纯输出文章ID,及对应的属性10086和属性12580的值,那么下面这个简单的SQL就能实现

SELECT
  art.id,
  meta1.meta_value AS meta_key10086,
  meta2.meta_value AS meta_key12580
FROM wp_posts AS art
  LEFT JOIN wp_postmeta AS meta1
    ON meta1.post_id = art.id AND meta1.meta_key = '10086'
  LEFT JOIN wp_postmeta AS meta2
    ON meta2.post_id = art.id AND meta2.meta_key = '12580'

你要的是不是 GROUP_CONCAT 这个聚合函数?

试试用full join行不。未测试:

with
  m1 as (select * from meta where meta_key = '10086'),
  m2 as (select * from meta where meta_key = '12580')

select case m1.post_id 
  when null then m2.post_id else m1.post_id end as post_id,
  m1.meta_value as meta_key_10086,
  m2.meta_value as meta_key_12580
from m1 full join m2
  on m1.post_id = m2.post_id and
     m1.meta_key='10086' and
     m2.meta_key='12580'
撰写回答
你尚未登录,登录后可以
  • 和开发者交流问题的细节
  • 关注并接收问题和回答的更新提醒
  • 参与内容的编辑和改进,让解决方法与时俱进
推荐问题