MySQL查询wordpress:每个类别最少100个帖子-删除其余的

Question

我想要一个SQL语句，将删除所有帖子，除了100最近的每个类别。

这似乎很容易——但是许多帖子存在于几个类别中，因此删除一个类别的101号帖子可能会删除另一个类别的89号帖子。

我试过使用mysql的"限制"但这在子查询中是不允许的。因此，我尝试使用ROW_NUMBER() OVER与rownumber >= 100代替，它在子查询中工作-但给出错误的结果。

，所以这适用于选择类别中所有的文章在过去100年:

SELECT * FROM (
SELECT
ROW_NUMBER() OVER (ORDER BY wp_posts.post_date DESC) AS rownumber
FROM wp_posts
WHERE (wp_posts.ID IN (SELECT object_id FROM wp_term_relationships WHERE term_taxonomy_id IN (71) ) ) 
AND (wp_posts.post_type = 'post' OR wp_posts.post_type = 'xdays1')  
) AS foo
WHERE rownumber >= 100

得到正确的结果数，29,602。

，但当我把它放在另一个查询，结果是错误的:

SELECT wp_posts.*
FROM  wp_posts 
WHERE 
wp_posts.ID IN
(SELECT * FROM (
SELECT
ROW_NUMBER() OVER (ORDER BY wp_posts.post_date DESC) AS rownumber
FROM wp_posts
WHERE (wp_posts.ID IN (SELECT object_id FROM wp_term_relationships WHERE term_taxonomy_id IN (71) ) ) 
AND (wp_posts.post_type = 'post' OR wp_posts.post_type = 'xdays1')  
) AS foo
WHERE rownumber >= 100)

结果只有10行。这个测试只是针对一个类别，我想对所有类别都这样做-所以我想如果我能在子查询中得到这个工作，我就可以添加其他类别。

我做错了什么?

Answer 1

总算修好了。这里使用了id为71、72、73、74的四个类别

SELECT wp_posts.*
FROM  wp_posts 

WHERE 
(wp_posts.post_type = 'post' AND wp_posts.post_status = 'publish')   

AND
-- posts discluded if under 100 most recent per category:
(
( wp_posts.ID NOT IN (SELECT ID FROM (SELECT ID, ROW_NUMBER() OVER (ORDER BY wp_posts.post_date DESC) AS rownumber
FROM wp_posts
WHERE (wp_posts.ID IN (SELECT object_id FROM wp_term_relationships WHERE term_taxonomy_id IN (71) ) ) 
AND (wp_posts.post_type = 'post' AND wp_posts.post_status = 'publish')  
) AS foo
WHERE rownumber <= 100
) 
)
AND
( wp_posts.ID NOT IN (SELECT ID FROM (SELECT ID, ROW_NUMBER() OVER (ORDER BY wp_posts.post_date DESC) AS rownumber
FROM wp_posts
WHERE (wp_posts.ID IN (SELECT object_id FROM wp_term_relationships WHERE term_taxonomy_id IN (72) ) ) 
AND (wp_posts.post_type = 'post' AND wp_posts.post_status = 'publish')  
) AS foo
WHERE rownumber <= 100
) 
)
AND
( wp_posts.ID NOT IN (SELECT ID FROM (SELECT ID, ROW_NUMBER() OVER (ORDER BY wp_posts.post_date DESC) AS rownumber
FROM wp_posts
WHERE (wp_posts.ID IN (SELECT object_id FROM wp_term_relationships WHERE term_taxonomy_id IN (73) ) ) 
AND (wp_posts.post_type = 'post' AND wp_posts.post_status = 'publish')  
) AS foo
WHERE rownumber <= 100
) 
)
AND
( wp_posts.ID NOT IN (SELECT ID FROM (SELECT ID, ROW_NUMBER() OVER (ORDER BY wp_posts.post_date DESC) AS rownumber
FROM wp_posts
WHERE (wp_posts.ID IN (SELECT object_id FROM wp_term_relationships WHERE term_taxonomy_id IN (74) ) ) 
AND (wp_posts.post_type = 'post' AND wp_posts.post_status = 'publish')  
) AS foo
WHERE rownumber <= 100
) 
)
)