mysql - 在sql中显示具有相同id的一行中的值

Question

我有三个表，我加入表并检索此查询中的所有值

sql查询:

 SELECT wp_term_relationships.object_id, wp_posts.post_title,                                        
 wp_postmeta.meta_key, wp_postmeta.meta_value
 FROM `wp_posts` , wp_postmeta, wp_term_relationships
 WHERE (wp_posts.ID = wp_postmeta.post_id)
 AND (wp_postmeta.post_id = wp_term_relationships.object_id)  
 AND wp_term_relationships.term_taxonomy_id =33

它的Show Table结构是这样的

object_id     post_title          meta_key           meta_value
 302       CHICKEN CHOW MEIN     post_image           url1
 302        CHICKEN CHOW MEIN      price              6.95

我想将 OBject Id 显示在一个列中，并将两行合并到一列中，我希望这样

object_id     post_title      meta_value(price)     meta_value(post_image)
302       CHICKEN CHOW MEIN         6.95                url1

Answer 1

您可以使用更常用的连接语法两次连接到 wp_postmeta

SELECT rel.object_id, p.post_title,
       meta_price.meta_key, meta_price.meta_value,
       meta_image.meta_key, meta_image.meta_value
FROM `wp_posts` p
    inner join wp_postmeta meta_price on meta_price.post_id = p.ID
    inner join wp_postmeta meta_image on meta_image.post_id = p.ID
    inner join wp_term_relationships rel on rel.objet_id = p.ID
WHERE rel.term_taxonomy_id = 33
    and meta_price.meta_key = 'price'
    and meta_image.meta_key = 'post_image'

(未检查语法 - 此处无法访问 WordPress 数据库 - 您需要稍微更改所选列。)