如何合并两个SQL查询以获取最新的WordPress博文和精选图片
我试图在ASP页面上显示最新的帖子摘录，帖子标题和精选图片。 为了简化查询，我将Permalink作为每个Post的自定义字段添加。 我有一个查询获取除精选图像以外的所有内容，并且我有另一个获取精选图像的查询，但我无法确定如何将它们合并到一个查询中。
//获取帖子摘录和帖子标题
SELECT
wp_posts.id,
wp_posts.post_title,
wp_postmeta.meta_value,
wp_postmeta.meta_key,
wp_posts.post_excerpt
FROM
wp_postmeta
INNER JOIN wp_posts p ON (wp_postmeta.post_id = wp_posts.ID)
WHERE post_id IN (
SELECT wp_posts.id
FROM wp_posts
WHERE post_type = 'post'
AND post_status = 'publish'
AND meta_key = 'my_permalink'
ORDER BY post_date, wp_posts.id
)
ORDER BY wp_posts.post_date DESC, wp_postmeta.post_id
LIMIT 2
//获得贴文的精选图片
SELECT p.*
FROM wp_postmeta AS pm
INNER JOIN wp_posts AS p ON pm.meta_value=p.ID
WHERE pm.post_id = $ID
AND pm.meta_key = '_thumbnail_id'
ORDER BY p.post_date DESC
任何人都可以帮我合并这些查询吗？ 谢谢。
要返回的样本数据：ID | post_title | post_excerpt | meta_value_my_permalink | featured_image_guid
**更新*我设法得到以下工作正常，除了我不能得到更多的一行，因为当我尝试在子查询中使用'IN'，例如pm2.post_id IN（SELECT wp_posts。 id FROM wp_posts WHERE post_type ='post'AND post_status ='publish'ORDER BY post_date DESC LIMIT 2）
SELECT
p.post_title,
p.post_excerpt,
pm.meta_value AS permalink,
p2.guid as thumbnail,
p2.post_title as image_alt
FROM
wp_postmeta pm
INNER JOIN wp_posts p ON (pm.post_id = p.ID),
wp_postmeta pm2
INNER JOIN wp_posts p2 ON (pm2.meta_value = p2.ID)
WHERE
pm.post_id = (SELECT wp_posts.id FROM wp_posts WHERE post_type = 'post' AND post_status = 'publish' ORDER BY post_date DESC LIMIT 1) AND
pm.meta_key = 'my_permalink' AND
pm2.post_id = (SELECT wp_posts.id FROM wp_posts WHERE post_type = 'post' AND post_status = 'publish' ORDER BY post_date DESC LIMIT 1) AND
pm2.meta_key = '_thumbnail_id'
没有一些样本数据很难看到你想要得到什么，但我认为你知道帖子ID并需要来自my_permakink和_thumbnail_id键的信息？ 由于我在平板电脑上，因此我只会为您提供裸露的骨骼，并让您限制结果集并对其进行过滤。
Select *
From wp_posts p
Inner join
Wp_postmeta pm1 on p.id = pm1.post_id and metakey = 'my_permalink'
Inner join
Wp_postmeta pm2 on p.id = pm1.post_id and metakey = '_thumbnail_id'
这将为您提供每个帖子都有一个图像和两个metakey的信息。 如果您希望没有图像的帖子的信息将第二个内部联接更改为左联接。
作为子查询的一堆连接和视图给了我我需要的东西。
SELECT
p.post_title,
p.post_excerpt,
pm.meta_value AS permalink,
p2.guid AS thumbnail,
p2.post_title AS thumbnail_alt
FROM
wp_postmeta pm
INNER JOIN wp_posts p ON (pm.post_id = p.ID)
INNER JOIN wp_postmeta pm2 ON (pm2.post_id = p.ID)
INNER JOIN wp_posts p2 ON (pm2.meta_value = p2.ID)
WHERE
pm.meta_key = 'my_permalink' AND
pm2.meta_key = '_thumbnail_id' AND
p.ID IN (SELECT * FROM vwLatestPostIds)
使用它来检索帖子标题，帖子ID，发布精选缩略图URL，帖子类别等 - “SELECT a.ID id，a.post_title title，a.post_content content，taxonomy，name，a.post_date_gmt postdate，max（c。 guid）img_url FROM kb_posts a JOIN kb_term_relationships tr ON（a.id = tr.object_id）JOIN kb_term_taxonomy tt ON（tr.term_taxonomy_id = tt.term_taxonomy_id AND tt.taxonomy ='category'）JOIN kb_terms t ON（t.term_id = tt .term_id AND t.term_id = $ user_id）
LEFT JOIN
(select post_parent, max(post_date_gmt) as latest_image_date from kb_posts
where post_type='attachment' GROUP BY post_parent) b
on a.id=b.post_parent
LEFT JOIN kb_posts c
on c.post_parent=a.id
and c.post_type='attachment'
and b.latest_image_date = c.post_date_gmt where c.guid IS NOT NULL
GROUP BY a.ID ORDER BY a.ID DESC LIMIT 5"
上一篇: How to merge two SQL queries to get latest Wordpress Post and Featured Image