删除重复行,按max(datetime)保留最近的行

w6mmgewl  于 2021-06-15  发布在  Mysql
关注(0)|答案(1)|浏览(377)

下面的mysql语句根据address1和postcode选择所有具有重复项的行,其中最近的一行是inspection\u date。基本上,它选择了所有我想要保留的。

  1. SELECT COUNT(*), MAX(INSPECTION_DATE), ADDRESS1, POSTCODE, id
  2. FROM epc
  3. GROUP BY ADDRESS1, POSTCODE
  4. HAVING COUNT(*) > 1;

我的问题是,如何删除所有旧的副本,保留上面语句返回的那些副本。

p4rjhz4m

p4rjhz4m1#

http://sqlfiddle.com/#!9月8日DE866/1

  1. DELETE e
  2. FROM epc e
  3. INNER JOIN epc max_
  4. ON e.address1 = max_.address1
  5. AND e.postcode = max_.postcode
  6. AND e.inspection_date < max_.inspection_date;

updatehttp://sqlfiddle.com/#!9/5bd5981/1号

  1. DELETE e
  2. FROM epc e
  3. INNER JOIN epc max_
  4. ON e.address1 = max_.address1
  5. AND e.postcode = max_.postcode
  6. AND (e.inspection_date < max_.inspection_date
  7. OR (e.inspection_date = max_.inspection_date
  8. AND e.id < max_.id));
展开查看全部

相关问题