php - MySQL查询；在连接中选择多个数据

Question

我有两个表，我想获取语言为 eng 且捐赠金额高于 €25 的所有(发布)记录。

这是构造查询；

SELECT wp_posts.* , wp_postmeta.* 
FROM wp_posts 
JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id 
WHERE (wp_postmeta.meta_key = 'lang_code' AND wp_postmeta.meta_value = 'en')
AND (wp_postmeta.meta_key = 'donated' AND CAST(wp_postmeta.meta_value AS SIGNED) > '25' ) 
AND wp_posts.post_type = 'donation' 
AND wp_posts.post_status = 'publish' 
ORDER BY wp_posts.post_date DESC
LIMIT 1 OFFSET 0;

表 #1 (wp_posts)

---------------------------------------------------
ID | name         | post_type  | post_status
---------------------------------------------------
01 | test entry 1 | donation   | publish
02 | test entry 2 | donation   | publish
03 | test entry 3 | donation   | awaiting

表#2 (wp_postmeta)；

---------------------------------------------------
meta_id | post_id | meta_key  | meta_value
---------------------------------------------------
23      | 01      | lang_code | en
84      | 01      | donated   | 3100
25      | 02      | lang_code | en
63      | 02      | donated   | 15

此演示数据的预期结果；

---------------------------------------------------
ID | name         | post_type  | post_status
---------------------------------------------------
01 | test entry 1 | donation   | publish

如何在没有子查询的情况下获得我需要的结果？

Answer 1

您可以使用两个联接来完成此操作:

SELECT p.* , pml.*, pmd.*  -- although you probably only want the value column
FROM wp_posts p JOIN
     wp_postmeta pml
     ON p.ID = pml.post_id AND
        pml.meta_key = 'lang_code' AND
        pml.meta_value = 'en' JOIN
     wp_postmeta pmd
     ON p.ID = pmd.post_id AND
        pmd.meta_key = 'donated' AND CAST(pmd.meta_value AS SIGNED) > 25 WHERE p.post_type = 'donation' AND p.post_status = 'publish' 
ORDER BY p.post_date DESC
LIMIT 1 OFFSET 0;