MySQL - 只有在同一个表中没有获胜者设置但不同的行时才选择帖子ID。 包含示例代码

Question

I have a wordpress table wp_postmeta and what I want is a list of all posts where sttf_split_test_status = 'active' but it CAN NOT have another meta row with the same post_id and a meta_key of stti_winner set. My below code returns empty, but there should be a result.

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

Answer 1

Try

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
;

Explanation: To every fitting record we try to join the corresponding 'stti_winner'. Only if that fails (fields of LEFT JOIN stay NULL), we accept the record.