我有以下表格的父子关系:
CREATE TABLE `pages` (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(100) NULL,
PRIMARY KEY ( id )
)
CREATE TABLE `pageObjects` (
id INT NOT NULL AUTO_INCREMENT,
object TEXT NULL,
lastChanged TIMESTAMP on update CURRENT_TIMESTAMP NOT NULL,
fkPageId int NOT NULL,
PRIMARY KEY ( id )
)
pages
与pageObjects
具有one:many关系。每当连接到单个页面的
pageObjects
记录超过10条时,必须删除过去编辑最远且超过10条的所有记录。我想在一个查询中执行此操作,但似乎无法弄清楚...
这是我走了多远:
DELETE
FROM pageObjects
WHERE id NOT IN (
SELECT po.id, po.fkPageId FROM (
SELECT objects.fkPageId FROM (
SELECT COUNT(*) as count, fkPageId
FROM pageObjects
GROUP BY fkPageId
) objects
WHERE count > 10
) AS page
JOIN pageObjects po
ON page.fkPageId = po.fkPageId
AND po.lastChanged < (
SELECT MIN(lastChanged )
FROM pageObjects
WHERE fkPageId = po.fkPageId
GROUP BY fkPageId
ORDER BY lastChanged DESC
LIMIT 10
)
)
可悲的是,底部子查询中的LIMIT位不能按我想要的方式工作,因为应在应用LIMIT之后应用MIN()函数。
所以我尝试了:
DELETE
FROM pageObjects
WHERE id NOT IN (
SELECT po.id, po.fkPageId FROM (
SELECT objects.fkPageId FROM (
SELECT COUNT(*) as count, fkPageId
FROM pageObjects
GROUP BY fkPageId
) objects
WHERE count > 10
) AS page
JOIN pageObjects po
ON page.fkPageId = po.fkPageId
AND po.lastChanged < (
SELECT MIN(lastChanged)
FROM (
SELECT lastChanged
FROM pageObjects
WHERE fkPageId = po.fkPageId
GROUP BY fkPageId
ORDER BY lastChanged DESC
LIMIT 10
)
)
)
但这是不可能的,因为po.fkPageId在子查询的子查询中不可用。
有没有办法做到这一点?
最佳答案
您可以通过计算每个id以后的条目数来非常简单地执行此操作:
DELETE FROM pageObjects
WHERE id IN (
SELECT id FROM pageObjects po
WHERE (
SELECT count(id)
FROM pageObjects po2
WHERE po2.fkPageId = po.fkPageId
AND po2.lastChanged > po.lastChanged
) > 10
)
在此处查看选择返回的内容:
http://www.sqlfiddle.com/#!9/f5218f/1/0
关于mysql - 如果计数超过10,则删除子记录,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/49688339/