表wp_posts+-----+----------+|ID|post_type|+-----+----------+|1|A||2|A||3|A||3|B||3|C|+-----+----------+
表wp_term_relationships(对象id=wp_posts.id)+-----------+------------------+|object_id | term_taxonomy_id|+-----------+------------------+|1|1||2|3||2|2||1|2||3|1|+-----------+-------------------+
表wp_esp_datetime(EVT_ID=wp_posts.ID)+-----------+---------------------+|EVT_ID|DTT_EVT_end|+-----------+---------------------+|2015年12月1日16:00:00||2015年12月2日16:00:00||3 | 2015-12-01 16:00:00|+-----------+---------------------+
现在我想做的是:从wp_esp_datetime中查询所有具有左联接日期时间的wp_posts,但仅查询那些在term_taxonomy_id=3的wp_term_relationships中没有条目的wp_post。我的问题是,wp_post.ID可以在wp_term_relationships.object_ID 中多次出现
我当前的查询如下:
SELECT * FROM wp_posts
INNER JOIN wp_term_relationships on wp_posts.ID = wp_term_relationships.object_id
LEFT JOIN wp_esp_datetime on wp_posts.ID = wp_esp_datetime.EVT_ID
WHERE wp_posts.post_type = 'A' GROUP BY wp_posts.ID
所以问题是:
我如何从wp_posts中排除在wp_term_relationships中具有term_taxonomy_id=3的任何条目的行,即使与另一个term_taxonomy(而不是3)匹配?
添加一个(不)存在的子句
SELECT * FROM wp_posts p
INNER JOIN wp_term_relationships tr on p.ID = tr.object_id
LEFT JOIN wp_esp_datetime ed on p.ID = ed.EVT_ID
WHERE p.post_type = 'A'
and not exists (select null
from wp_term_relationships
where term_taxonomy_id = 3
and p.ID = object_id)
GROUP BY p.ID
是否尝试添加子查询?
AND wp_posts.ID NOT IN
(SELECT wtr2.object_id FROM wp_term_relationships wtr2 WHERE wtr2.term_taxonomy_id = 3)
您的查询已编辑:
SELECT *
FROM wp_posts p
INNER JOIN wp_term_relationships wtr on p.ID = wtr.object_id
LEFT JOIN wp_esp_datetime wed on p.ID = wed.EVT_ID
WHERE p.post_type = 'A'
AND p.ID NOT IN (SELECT wtr2.object_id FROM wp_term_relationships wtr2 WHERE wtr2.term_taxonomy_id = 3)
GROUP BY p.ID
使用not exists
并删除带有wp_term_relationships:的联接
SELECT p.ID, ed.DTT_EVT_end
FROM wp_posts p
LEFT JOIN wp_esp_datetime ed on p.ID = ed.EVT_ID
WHERE p.post_type = 'A'
AND NOT EXISTS (
SELECT 1
FROM wp_term_relationships
WHERE term_taxonomy_id = 3
AND p.ID = object_id)
ORDER BY p.ID, ed.DTT_EVT_end;
我看不出以下问题未能解决的部分。。。
SELECT p.*
, d.*
FROM wp_posts p
LEFT
JOIN wp_esp_datetime d
ON d.evt_id = p.id
LEFT
JOIN wp_term_relationships r
ON r.object_id = p.id
AND r.term_taxonomy_id = 3
WHERE term_taxonomy_id IS NULL;
这将产生以下结果。。。
+----+-----------+--------+---------------------+
| ID | post_type | EVT_ID | DTT_EVT_end |
+----+-----------+--------+---------------------+
| 1 | A | 1 | 2015-12-01 16:00:00 |
| 3 | A | 3 | 2015-12-01 16:00:00 |
| 3 | B | 3 | 2015-12-01 16:00:00 |
| 3 | C | 3 | 2015-12-01 16:00:00 |
+----+-----------+--------+---------------------+
将GROUP BY子句应用于该结果集似乎毫无意义。