我正在尝试从需要执行 3 个条件的wp_posts表中选择 ID。
-
ID 是否与wp_postmeta表中的post_id匹配,其中 meta_key = 'gtp_analytics_client_id' 并且meta_value不为空。
-
ID 是否与wp_postmeta表中的post_id匹配,其中 meta_key = 'gtp_conversion_uploaded' 且meta_value不等于 1。
- ID 是否与wp_postmeta表中的post_id匹配,其中 meta_key = 'gtp_lead_revenue' 并且meta_value不为空。
我是SQL的初学者。这就是我现在拥有的,但我不能使用多个 IN。所以我认为我需要用另一种方式来做。
SELECT ID
FROM wp_posts
WHERE ID IN (SELECT post_id
FROM wp_postmeta
WHERE meta_key = 'gtp_analytics_client_id' AND meta_value != '')
AND IN (SELECT post_id
FROM wp_postmeta
WHERE meta_key = 'gtp_conversion_uploaded' AND meta_value != 1)
AND IN (SELECT post_id
FROM wp_postmeta
WHERE meta_key = 'gtp_revenue' AND meta_value != '')
我收到以下错误:
您的SQL语法有错误;请查看与您的MySQL服务器版本相对应的手册,了解在第4行的"IN (SELECT post_id FROM wp_postmeta,其中meta_key = 'gtp_conversion_uploaded' A'附近使用的正确语法
and
不是in
运算符的一部分,它是三个独立的in
运算符,因此您需要所有操作数的第一个操作数(ID
SELECT ID
FROM wp_posts
WHERE ID IN ( ... )
AND ID IN ( ... )
AND ID IN ( ... )
您也可以将其写为三个连接:
SELECT
p.ID
FROM
wp_posts p
INNER JOIN wp_postmeta m1 ON m1.post_id = p.ID AND m1.meta_key = 'gtp_analytics_client_id' AND m1.meta_value != ''
INNER JOIN wp_postmeta m2 ON m2.post_id = p.ID AND m2.meta_key = 'gtp_conversion_uploaded' AND m2.meta_value != 1
INNER JOIN wp_postmeta m3 ON m3.post_id = p.ID AND m3.meta_key = 'gtp_revenue' AND m3.meta_value != ''
当它可以是 3 种情况之一时
SELECT ID
FROM wp_posts
WHERE ID IN (SELECT post_id
FROM wp_postmeta
WHERE (meta_key = 'gtp_analytics_client_id' AND meta_value != '')
OR (meta_key = 'gtp_conversion_uploaded' AND meta_value != 1)
OR (meta_key = 'gtp_revenue' AND meta_value != '')
)
SELECT p.ID
FROM wp_posts p
JOIN wp_postmeta m on p.id = m.post_id
group by p.id
having sum(m.meta_key = 'gtp_analytics_client_id' AND m.meta_value != '') > 0
and sum(m.meta_key = 'gtp_conversion_uploaded' AND m.meta_value != 1) > 0
and sum(m.meta_key = 'gtp_revenue' AND m.meta_value != '') > 0