结合两个 sql 查询 - [英] combine two sql queries -

问题描述

我有两个正在尝试合并的 sql 查询

第一个:

SELECT * FROM wp_posts在 (post_id=ID) 上加入 wp_postmetaWHERE meta_key = "packageID" 和 meta_value = 1ORDER BY post_date 限制 50

将 wordpress wp_post 表加入 wp_postmeta 并获取 packageID = 1 的所有帖子(我认为这可能是一种不雅的方式，但它有效)

第二个

SELECT * FROM wp_postmetaJOIN wp_posts ON (meta_value=ID)哪里 post_id = 2110AND meta_key = '_thumbnail_id'

再次将 wp_post 表连接到 wp_postmeta 表，因此对于 id 为 2110 的帖子，它成功获取了该帖子的缩略图.NB 2110 只是一个 id 的例子

在 Wordpress 中，缩略图是一种帖子.因此，在此示例中，构成帖子 2110 的文本与帖子 2115 相关联 - 后者是缩略图

我想要做的是获取第一个查询中的列表，同时获取与每个帖子关联的缩略图

我想我需要两个连接，但我不知道怎么做(作为一个 sql 初学者)

注意这将在 Wordpress 之外的脚本中，所以我不能使用 Wordpress 的内置函数

解决方案

你可以试试这个，如果帖子的缩略图不止一个，你可以得到用逗号分隔的缩略图列表

SELECT*,(选择GROUP_CONCAT(元值)从wp_postmeta哪里 post_id = wp.IDAND wpm.meta_key = "_thumbnail_id") AS `thumbnails`从wp_posts wp加入 wp_postmeta wpm开启 (wpm.post_id = wp.ID)WHERE wpm.meta_key = "packageID"和 wpm.meta_value = 1按 wp.post_date 订购限制 50

<块引用>

注意:GROUP_CONCAT 对连接字符有限制，但您可以增加这个限制

要只获取一个缩略图，您可以试试这个

SELECT*,(选择(元值)从wp_postmeta哪里 post_id = wp.ID和 wpm.meta_key = "_thumbnail_id" 限制 1)从wp_posts wp加入 wp_postmeta wpm开启 (wpm.post_id = wp.ID)WHERE wpm.meta_key = "packageID"和 wpm.meta_value = 1按 wp.post_date 订购限制 50

I've have two sql queries which I'm trying to combine

The first:

SELECT * FROM wp_posts
JOIN wp_postmeta on (post_id=ID)
WHERE  meta_key = "packageID" and  meta_value = 1 
ORDER BY post_date limit 50

Joins the wordpress wp_post table to the wp_postmeta and gets all the posts meeting with packageID = 1 (I think it might be an inelegant way of doing it but it works)

The second

SELECT * FROM wp_postmeta
JOIN wp_posts ON (meta_value=ID) 
WHERE post_id = 2110
AND meta_key = '_thumbnail_id'

again joins the wp_post table to the wp_postmeta table, so for the post with the id 2110 it successfully gets the thumbnail for that posts. NB 2110 is just an example of an id

In Wordpress a thumbnail is a kind of post. So in this example the text which constitutes post 2110 is a associated with post 2115 - the latter being the thumbnail

What I'm trying to do is get the list as in the first query but also get thumbnails associated with each post

I think I need two joins but I can't see how to do it (being an sql beginner)

NB this will be in a script outside Wordpress so I can't use Wordpress's built-in functions

解决方案

You can try this one,if there are more than one thumbnails for the post you can get the list of thumbnails separated by comma

SELECT 
  *,
  (SELECT 
    GROUP_CONCAT(meta_value) 
  FROM
    wp_postmeta 
  WHERE post_id = wp.ID 
    AND wpm.meta_key = "_thumbnail_id") AS `thumbnails`
FROM
  wp_posts wp 
  JOIN wp_postmeta wpm 
    ON (wpm.post_id = wp.ID) 
WHERE wpm.meta_key = "packageID" 
  AND wpm.meta_value = 1 
ORDER BY wp.post_date 
LIMIT 50 

Note : GROUP_CONCAT has a limit to concat characters but you can increase this limit

To get only one thumbnail you can try this

SELECT 
  *,
  (SELECT 
    (meta_value) 
  FROM
    wp_postmeta 
  WHERE post_id = wp.ID 
    AND wpm.meta_key = "_thumbnail_id" LIMIT 1) 
FROM
  wp_posts wp 
  JOIN wp_postmeta wpm 
    ON (wpm.post_id = wp.ID) 
WHERE wpm.meta_key = "packageID" 
  AND wpm.meta_value = 1 
ORDER BY wp.post_date 
LIMIT 50 

这篇关于结合两个 sql 查询 -的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！