时间戳

首页 > 解决方案 > 组合两个选择语句（追加列）

mysql - 组合两个选择语句（追加列）

问题描述

我有 2 个 Wordpress 数据库查询，第一个显示所有帖子 + 类别。第二个显示所有帖子+自定义字段。

我正在寻找的是列出所有帖子+类别+自定义字段

查询一：列出帖子+分类

SELECT ID,
post_title,

(SELECT group_concat(wp_terms.name separator ", ") FROM wp_terms
INNER JOIN wp_term_taxonomy on wp_terms.term_id = 
wp_term_taxonomy.term_id
INNER JOIN wp_term_relationships wpr on wpr.term_taxonomy_id = 
wp_term_taxonomy.term_taxonomy_id
WHERE taxonomy="category" and wp_posts.ID = wpr.object_id ) AS 
"Categories"
FROM wp_posts WHERE post_type = "post" AND post_status = "publish"

+------+--------------------------------------+------------+
| ID   | post_title                           | Categories |
+------+--------------------------------------+------------+

查询2：列出帖子+自定义字段

SELECT ID, post_title, pm1.meta_value as "Amazon.com", pm2.meta_value 
as "Amazon.co.uk" FROM wp_posts
LEFT JOIN wp_postmeta pm1 ON ID = pm1.post_id AND 
pm1.meta_key = "Amazon.com"
LEFT JOIN wp_postmeta pm2 ON ID = pm2.post_id AND 
pm2.meta_key = "Amazon.co.uk"
WHERE post_type = "post" AND post_status = "publish"

+------+-----------------------+--------------+--------------+
| ID   | post_title            | Amazon.com   | Amazon.co.uk |
+------+-----------------------+--------------+--------------+

如何组合结果以便列出所有帖子 + 类别 + 自定义字段？

+------+-------------+-------------+--------------+--------------+
| ID   | post_title  | Categories  | Amazon.com   | Amazon.co.uk |
+------+-------------+-------------+--------------+--------------+

标签： mysqlsqlwordpresswp-cli

解决方案

尝试使用以下查询：

SELECT ID, post_title, pm1.meta_value as "Amazon.com", pm2.meta_value as "Amazon.co.uk" , 
                                        (SELECT group_concat(wp_terms.name separator ", ") FROM wp_terms
                                        INNER JOIN wp_term_taxonomy on wp_terms.term_id = 
                                        wp_term_taxonomy.term_id
                                        INNER JOIN wp_term_relationships wpr on wpr.term_taxonomy_id = 
                                        wp_term_taxonomy.term_taxonomy_id
                                        WHERE taxonomy="category" and wp_posts.ID = wpr.object_id ) AS "Categories"
FROM wp_posts
LEFT JOIN wp_postmeta pm1 ON ID = pm1.post_id AND 
pm1.meta_key = "Amazon.com"
LEFT JOIN wp_postmeta pm2 ON ID = pm2.post_id AND 
pm2.meta_key = "Amazon.co.uk"
WHERE post_type = "post" AND post_status = "publish"

推荐阅读