mysql - 如何查询在另一个表中有许多关联行的记录被视为一行

Question

我有一张wp_posts桌子：

id
post_title
post_status
post_type

和wp_postmeta表：

meta_id
post_id
meta_key
meta_value

我想查询此表以表示为：

post_id |     a_meta_key_from_postmeta_col             | another_meta_key
--------------------------------------------------------------------------------------
   1    |    value_of_the_meta_key_assotated           | value_of_the_other_key_assotated_with_this_post
   2    |    ......                                    | ...

SELECT wp_posts.id, wp_postmeta.meta_key, wp_postmeta.meta_value 
FROM `wp_posts` 
INNER JOIN wp_postmeta ON wp_posts.id=wp_postmeta.post_id 
WHERE wp_posts.post_type='foo' 
AND post_status='publish' 
AND (wp_postmeta.meta_key='some_key' 
OR wp_postmeta.meta_key='another_some_key';

这是结果

id     | meta_key         | meta_value
 1     | some_key         | some_value
 1     | another_key      | another_value
 2     | some_key         | some_value
 ...   | ...              |   ...

这不是我想要的。

（我的最终目标是将其导出到 csv 文件）

标签： mysqlphpmyadmin

Answer 1

如果要导出到 csv 文件，则应使用into outfile语句。

SELECT wp_posts.id, wp_postmeta.meta_key, wp_postmeta.meta_value 
FROM wp_posts
INNER JOIN wp_postmeta ON wp_posts.id=wp_postmeta.post_id 
WHERE wp_posts.post_type='foo' 
AND post_status='publish' 
AND wp_postmeta.meta_key='some_key' 
OR wp_postmeta.meta_key='another_some_key' 
into outfile './export.csv' fields terminated by ';' lines terminated by '\n';

如果您想在每列结果之前添加一些文本，请使用CONCAT_WS()：https ://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_concat-ws

SELECT wp_posts.id, CONCAT_WS("_","a_key_from",wp_postmeta.meta_key) ,CONCAT_WS("_","a_value_from",meta_value)  
FROM wp_posts 
INNER JOIN wp_postmeta ON wp_posts.id=wp_postmeta.post_id 
WHERE wp_posts.post_type='foo' 
AND post_status='publish' 
AND wp_postmeta.meta_key='some_key' 
OR wp_postmeta.meta_key='another_some_key' 
into outfile './export.csv' fields terminated by ';' lines terminated by '\n';

第二个查询结果如下所示：

进入outfile语句示例：