mysql - 复杂的mysql查询，连接表并将多行显示为一行

Question

我有以下设置

TABLE WP_POSTS

 ID   POST_TYPE  
====  =========
6891  trainer  
6896  trainer  
...  

TABLE WP_POSTMETA  

POST_ID   META_KEY   META_VALUE  
=======   ========   ==========
6891      lat        48.205206   
6891      long       11.326714  
6896      lat        23.5454    
6896      long       25.343434 
...  

我的目标是为 post_type trainer 的每个 wp_posts 条目添加一行，其中包含 post_id 以及 meta_key lat 和 long 的 meta_value

所以我想要的结果应该是这样的

id     lat    long
6891   48...  11....
6896   23...  25....

现在我得到以下内容

id      meta_key    meta_value  
====    ========    ==========
6891    lat         48.205206   
6896    lat         23.5454 
6891    long        11.326714   
6896    long        25.343434   

使用以下sql查询

SELECT wp_posts.id, wp_postmeta.meta_key, wp_postmeta.meta_value  
FROM `wp_posts` 
     INNER JOIN wp_postmeta ON wp_postmeta.post_id = wp_posts.id 
WHERE wp_posts.post_type = 'trainer' 
      AND (wp_postmeta.meta_key = 'lat' OR wp_postmeta.meta_key = 'long')

是否可以将纬度和经度排成一行？

感谢您的阅读! :-)

Answer 1

Untested (and may not work because of your db setup, no way to control the order of the rows being retrieved - poor design - lat and long should be on the same row): SELECT wp.id, GROUP_CONCAT(wp.meta_value) AS 'Lat/Long' FROM wp_posts as wp, wp_postsmeta AS wpm WHERE (wp.id=wpm.id AND wpm.post_type='trainer')