只要有超过YY行，sql语句就会删除XXX以前的记录 [英] sql statement to delete records older than XXX as long as there are more than YY rows

问题描述

假设具有以下列的表：

pri_id ， item_id ，评论，日期

我想要的是一个SQL查询，将删除任何比给定日期更早的特定 item_id 的记录，但只要有超过15个 item_id 的行。

What I want to have is a SQL query that will delete any records, for a specific item_id that are older than a given date, BUT only as long as there are more than 15 rows for that item_id.

这将用于清除项目1年以上的评论记录，但我仍然希望在任何时候保留至少15条记录。这样，如果我有一个评论10年，它永远不会被删除，但如果我在过去5天有100条评论，我只保留最新的15条记录。这个例子当然是任意的记录计数和日期时间框架。

This will be used to purge out comment records older than 1 year for the items but I still want to keep at least 15 records at any given time. This way if I had one comment for 10 years it would never get deleted but if I had 100 comments over the last 5 days I'd only keep the newest 15 records. These are of course arbitrary record counts and date timeframes for this example.

我想找到一个非常通用的方法，可以在mysql，oracle， Postgres等我使用phps adodb库进行数据库抽象，所以如果可能，我希望它能够正常工作。

I'd like to find a very generic way of doing this that would work in mysql, oracle, postgres etc. I'm using phps adodb library for DB abstraction so I'd like it to work well with that if possible.

推荐答案

p>这样的东西应该适合你：

Something like this should work for you:

delete
from
  MyTable
where
  item_id in
  (
    select
      item_id
    from
      MyTable
    group by
      item_id
    having
      count(item_id) > 15
  )
  and
    Date < @tDate

这篇关于只要有超过YY行，sql语句就会删除XXX以前的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！