使用分组和聚合条件删除

I have a table that includes a foreign key and a DATETIME timestamp. I want to delete all rows where the timestamp is later than the beginning of the day of the final timestamp, but for each item separately.

我有一个包含外键和DATETIME时间戳的表。我想删除所有的行，其中的时间戳比最终时间戳的开始时间晚，但是每个条目都是单独的。

Logically I want something like this:

逻辑上，我想要的是这样的东西:

 DELETE FROM Entries
 WHERE StartTime > CONVERT(Date,MAX(StartTime))
 GROUP BY ItemId;

But I can't use GROUP BY in a DELETE, nor can I use SUM() in a WHERE condition.

但是我不能在删除中使用GROUP BY，也不能在WHERE条件下使用SUM()。

I can delete them one item at at time using a subquery like this:

我可以一次删除一个项目，使用这样的子查询:

 DELETE FROM Entries
 WHERE ItemId=@Id
 AND StartTime > (
   SELECT CONVERT(Date,MAX(StartTime)) FROM Entries
   WHERE ItemId=@Id
 )

But I want to delete for all items in a single query. How can I achieve this?

但是我想要删除一个查询中的所有项。我如何做到这一点?

2 个解决方案

#1

You can rewrite your query to not use @Id:

您可以重写查询，不使用@Id:

DELETE e
FROM Entries AS e
WHERE StartTime > (
   SELECT CONVERT(Date,MAX(StartTime)) FROM Entries ee
   WHERE ee.ItemId=e.Id
)

#2

For your query you can use

对于您的查询，您可以使用。

 DELETE FROM E
 FROM Entries E JOIN (
 SELECT ItemId, MAX(StartTime) st from Entries GROUP BY ItemId)E2
 ON E.ItemId=E2.ItemId 
 WHERE E.StartTime > CONVERT(Date,E2.st)

#1