MySQL如何从同一列中选择多个值以使它们出现在不同的列中？

问题描述：

wp_posts 

ID post_title      post_date 
20 Título de tuit de ejemplo  2012-12-03 20:24:58 
23 Título de teletipo de ejemplo 2012-12-04 13:56:33 
25 Título de Clipping de ejemplo 2012-12-04 14:16:14 
28 Título 4 diciembre 2012   2012-12-04 14:48:05 
32 Título de noticia Actualidad 2012-12-04 17:35:57 
78 Título de Destacado de ejemplo 2012-12-05 23:19:38 
110 Título de Clipping radio  2012-12-09 15:57:28 
112 Título de Clipping televisión 2012-12-09 15:59:31

而且wp_postmeta

meta_id post_id meta_key   meta_value 
61  20  wpcf-custom-author John Doe 
134  28  wpcf-custom-author John Smith 
85  23  wpcf-custom-author  
111  25  wpcf-custom-author  
176  32  wpcf-custom-author John Smith 
614  110  wpcf-custom-author  
524  78  wpcf-custom-author  
627  112  wpcf-custom-author  
538  80  wpcf-custom-author  
48  20  wpcf-source  
132  28  wpcf-source   El País 
83  23  wpcf-source   El País 
109  25  wpcf-source   El País 
174  32  wpcf-source   El País 
612  110  wpcf-source   El Mundo

这是正确的，这些都是WordPress的表，但我的问题是关于MySQL的，我需要它检索从wp_postmeta几个字段属于查询相同的wp_posts.ID，这样我就可以获得同一行的所有信息。到目前为止，我想出了：

SELECT DISTINCT posts.post_title AS title, 
posts.post_date AS publish_date, 
(SELECT subpostmeta.meta_value FROM wp_postmeta AS subpostmeta WHERE subpostmeta.post_id = postmeta.post_id AND subpostmeta.meta_key = 'wpcf-lead-in') AS lead_in, 
(SELECT subpostmeta.meta_value FROM wp_postmeta AS subpostmeta WHERE subpostmeta.post_id = postmeta.post_id AND subpostmeta.meta_key = 'wpcf-custom-body') AS custom_body, 
(SELECT subpostmeta.meta_value FROM wp_postmeta AS subpostmeta WHERE subpostmeta.post_id = postmeta.post_id AND subpostmeta.meta_key = 'wpcf-custom-author') AS custom_author, 
(SELECT subpostmeta.meta_value FROM wp_postmeta AS subpostmeta WHERE subpostmeta.post_id = postmeta.post_id AND subpostmeta.meta_key = 'wpcf-source') AS source 
FROM wp_posts AS posts, wp_postmeta AS postmeta 
WHERE posts.ID = postmeta.post_id 
AND posts.post_status = 'publish' 
AND posts.post_type = 'contenido' 
AND posts.post_date < NOW() 
ORDER BY posts.post_date DESC

这是因为据我所知工作正常，因为我越来越：

post_title       post_date    lead_in             custom_body            custom_author source 
Título de noticia de ejemplo  2013-02-04 15:10:25  En un lugar de la Mancha, de cuyo nombre no quiero... En un lugar de la Mancha, de cuyo nombre no quiero... Juan García  ABC 
Título de Clipping televisión  2012-12-09 15:59:31                Clipping TV: Your bones don't break, mine do. That...     La Vanguardia 
Título de Clipping radio   2012-12-09 15:57:28                Clipping radio: Now that we know who you are, I kn...     El Mundo 
Título de Destacado de ejemplo  2012-12-05 23:19:38                Really?! We'll go deliver this crate like professi...

但是我不知道这是否是最好的方法来得到我想要的，我已经阅读了有关左连接并在过去使用它，但不知道我是否可以在这里使用它或优点是什么。

欢迎任何反馈！

编辑：我终于使用了左加入，所以我不知道如何投票或关闭此主题。它有一点比我先写到这里比较复杂，反正这是我的最终查询：

SELECT DISTINCT posts.*, 
     posts.post_date     AS publish_date, 
     tbl_lead_in.meta_value   AS lead_in, 
     tbl_custom_author.meta_value  AS custom_author, 
     tbl_source.meta_value   AS source, 
     tbl_category_terms.slug   AS category_term_slug, 
     tbl_category_terms.term_id AS category_term_id 
    FROM $wpdb->posts AS posts 
     LEFT JOIN $wpdb->postmeta AS tbl_lead_in 
       ON (posts.ID = tbl_lead_in.post_id 
        AND tbl_lead_in.meta_key = 'wpcf-lead-in') 
     LEFT JOIN $wpdb->postmeta AS tbl_custom_body ON 
        (posts.ID = tbl_custom_body.post_id 
        AND tbl_custom_body.meta_key = 'wpcf-custom-body') 
     LEFT JOIN $wpdb->postmeta AS tbl_custom_author 
       ON (posts.ID = tbl_custom_author.post_id 
        AND tbl_custom_author.meta_key = 'wpcf-custom-author') 
     LEFT JOIN $wpdb->postmeta AS tbl_source 
       ON (posts.ID = tbl_source.post_id 
        AND tbl_source.meta_key = 'wpcf-source') 
     LEFT JOIN $wpdb->term_relationships AS tbl_category_rel 
       ON (posts.ID = tbl_category_rel.object_id) 
     RIGHT JOIN $wpdb->term_taxonomy AS tbl_category 
       ON (tbl_category_rel.term_taxonomy_id = tbl_category.term_taxonomy_id 
        AND tbl_category.taxonomy = 'category') 
     LEFT JOIN $wpdb->terms AS tbl_category_terms 
       ON (tbl_category_terms.term_id = tbl_category.term_id) 
    WHERE posts.post_status = 'publish'

你可能想看看在转动查询。我在MySQL中对他们不太熟悉，所以我不能再帮忙。但谷歌搜索可以帮助。 – 2013-03-02 21:49:45

答

您正试图枢从行数据转换成列中的数据。 MySQL不具有旋转功能，但是你可以重写查询中使用聚合函数与CASE表达对每个要返回这些列：

SELECT p.post_title, 
    max(case when pm.meta_key = 'wpcf-lead-in' then pm.meta_value end) as lead_in, 
    max(case when pm.meta_key = 'wpcf-custom-body' then pm.meta_value end) as custom_body, 
    max(case when pm.meta_key = 'wpcf-custom-author' then pm.meta_value end) as custom_author, 
    max(case when pm.meta_key = 'wpcf-source' then pm.meta_value end) as source 
FROM wp_posts p 
LEFT JOIN wp_postmeta pm 
    ON p.id = pm.post_id 
WHERE p.post_status = 'publish' 
    AND p.post_type = 'contenido' 
    AND p.post_date < NOW() 
GROUP BY p.post_title 
ORDER BY p.post_date DESC

MySQL如何从同一列中选择多个值以使它们出现在不同的列中？

相关推荐