删除旧记录,同时保持每个家长的最低记录数
我的问题类似于sql statement to delete records older than XXX as long as there are more than YY rows,但该问题只处理单个父母,我想一次删除所有父母的记录。删除旧记录,同时保持每个家长的最低记录数
考虑一下这个表:
CREATE TABLE Children
(
ChildId int NOT NULL,
ChildCreated datetime NOT NULL,
ParentId int NOT NULL
)
这可能是任何亲子关系,所以名称是通用的。
我想删除所有超过一个月的孩子,但需要为每位家长保留最少的孩子数量,而不管他们的年龄。
我尝试了一些嵌套SELECT和GROUP BY的语句,它们给了我一些结果,但没有给我提供正确的结果集。
因为我使用SQL服务器,我想出了以下解决方案,它的伟大工程:
WITH CTE AS
(
SELECT ROW_NUMBER() OVER (Partition BY ParentId ORDER BY ChildCreated DESC)
As RowNo, ChildCreated FROM Children
)
DELETE FROM CTE WHERE RowNo > 10
AND RevisionCreated < DATEADD(MONTH,-1,GetDate())
公共表表达式组对每个父母在一起的所有儿童,并增加了根据创建顺序上连续的行号。每个父母的最新孩子有 行号为1,第十个最新的有10个。所以我可以删除所有行 数字大于10的所有记录,只要他们也超过一个月大。
我的问题是,如果我不得不在CTE不受支持的系统上做同样的事情。什么是解决这个问题的ANSI SQL-92解决方案?
基于其它响应,和我的查询相对简单,我想我可能是在简化的问题,但我既然的parentID假设不能为空,它没有引用childID的,在这种情况下,可以实现简单,如下面的
DELETE Children
FROM Children a
WHERE ChildCreated < DATEADD(MONTH, -1, GETDATE())
AND ( SELECT COUNT(*) -- NUMBER OF NEWER CHILDREN WITH THE SAME PARENT
FROM Children b
WHERE a.ParentID = b.ParentID
AND ( a.ChildCreated < b.ChildCreated
OR (a.ChildCreated = b.ChildCreated AND a.ChildID > b.ChildID)
)
) >= 10
虽然这个确切的SQL可能需要根据RDBMS的调整,我不知道任何RDBMS中不能应用此主体的情况。
以外,您当前的版本适用于独特的“ChildCreated”值,这似乎不起作用,因为您排除了所有在创建后截止日期,只看旧的。考虑一下你的子查询返回的结果,可能是最老的孩子返回11,所以它有资格,但是对于第二个最老的孩子返回10(因为最老的一个不比第二个最旧),所以第二个最早的出线。 – 2012-02-10 12:29:19
我已经稍微编辑了答案,在子查询之后将“>”更改为“> =”,并为具有相同时间戳的同一父项的两个子项进行了补贴。我不完全理解你的评论,除非它指的是我在子查询之后所做的“> =”错误,所以我不知道如何改进我的答案。我发布的查询将删除比一个月以下的孩子晚于至少10个新的孩子的父母。据我所知,这是你需要的标准?我打算把它误解为我误解了这个问题。 – GarethD 2012-02-10 13:30:38
这一个工作,所有三个查询现在删除同一组记录。你甚至可以用于重复的ChildCreated值。谢谢。 – 2012-02-10 15:15:23
很可能拖累了性能,但下面的语句
- 更增添了
rownumber
,重新启动为一组,通过使用子查询对同一组的孩子的的计数每个记录。 - 返回一个内存表,其中包含
ChildId
的给定rownumber。 -
JOIN
回原始表上ChildId
- 增加了一个
WHERE
子句在那里你可以给出rownumber
和/或其他列过滤来自Children
。 - 使用
DELETE FROM
语句中的结果。
SQL语句
DELETE FROM Children
FROM Children c
INNER JOIN (
SELECT ChildId
, (SELECT COUNT(*) + 1
FROM Children rn
WHERE rn.ChildCreated < Children.ChildCreated
AND rn.ChildId = Children.ParentId
) AS rn
FROM Children
) rn ON rn.ChildId = c.ChildId
WHERE rn.rn > 10
AND ChildCreated < DATEADD(MONTH,-1,GetDate())
有趣的是,有几点:1.'FROM children FROM Children c'语法对我来说看起来不正确,我认为你可以删除第一个'FROM children'部分。 2.您将rn.ChildId与Children.ChildId进行比较,后者假定Ids是按创建顺序排列的,将rn.ChildCreated与Children.ChildCreated进行比较会更安全。 3.为什么COALESCE,不应该总是不能为NULL的ParentId?否则,它似乎工作,但确实很慢。 – 2012-02-10 11:31:46
我在实践中测试了这个,它有着稍微不同的要求。 ChildCreated字段实际上是LastModified字段,因此我不能依赖ChildId的顺序并必须比较LastModified字段,问题是父母可能在LastModified中有两个具有相同值的子元素。如果第10个和第11个最大的孩子具有相同的LastModified值,则不包括它们。但是,如果你有独特的ID,它应该工作。 – 2012-02-10 12:39:29
@PeterHahndorf - 1. DELETE FROM Children FROM是有效的语法afaik。 2.你比较ChildCreated日期是正确的。 3.对此
就我所知,CTEs *在ANSI SQL 99标准中是有效的。也许你想要一个不支持标准的数据库解决方案? (MySQL也许?) – 2012-02-10 08:42:31
@MarkByers我不知道CTE在SQL-99中,是的,我会对不支持CTE的系统解决方案感兴趣。我改变了问题,要求SQL-92。 – 2012-02-10 08:56:06