我有一个wordpress表wp_postmeta,我想要的是sttf_split_test_status = 'active'
的所有帖子的列表,但它不能有另一个设置了相同post_id和stti_winner
的meta_key的元行。我下面的代码返回为空,但应该有一个结果。
SELECT DISTINCT post_id
FROM wp_postmeta
WHERE meta_key = 'sttf_split_test_status'
AND meta_value = 'active'
AND NOT EXISTS (
SELECT post_id
FROM wp_postmeta
WHERE post_id = post_id
AND meta_key = 'stti_winner'
)
LIMIT 0, 25
尝试
SELECT DISTINCT yes.post_id
FROM wp_postmeta AS yes
LEFT JOIN wp_postmeta AS no
ON yes.post_id=no.post_id
AND no.meta_key='stti_winner'
WHERE yes.meta_key = 'sttf_split_test_status'
AND yes.meta_value = 'active'
AND no.post_id IS NULL
LIMIT 0, 25
;
说明:对于每个合适的记录,我们都会尝试加入相应的"stti_winner"。只有当失败时(LEFT JOIN
的字段保持为NULL
),我们才接受该记录。