sql去掉重复记录的查询-优快云博客

本文介绍了一种SQL技巧，用于从表中选择最新的记录，并去除那些具有相同matchid和chipintypeid但时间戳不同的重复记录。通过两种方法实现这一目标：一是使用子查询来找出每个匹配项的最晚更新时间；二是使用not exists条件来排除所有更新时间早于当前记录的重复项。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

有一个表: tableA

ID,mathid,chipintypeid,otherpl,ascdesc,changedate

2 100 1 '1.0||2.0' '1||2' '2009-03-18 15:20:1'
3 102 1 '1.0||2.0' '1||2' '2009-03-18 15:20:2'
4 103 1 '1.0||2.0' '1||2' '2009-03-18 15:20:3'
5 100 1 '1.0||2.0' '1||2' '2009-03-18 15:20:4'
6 100 1 '1.0||2.0' '1||2' '2009-03-18 15:20:5'
7 104 1 '1.0||2.0' '1||2' '2009-03-18 15:20:6'
8 103 1 '1.0||2.0' '1||2' '2009-03-18 15:20:7'
9 100 2 '1.0||2.0' '1||2' '2009-03-18 15:20:8'
10 100 5 '1.0||2.0' '1||2' '2009-03-18 15:20:9'
11 104 1 '1.0||2.0' '1||2' '2009-03-18 15:20:10'
12 100 1 '1.0||2.0' '1||2' '2009-03-18 15:20:10'

现要取得

12 100 1 '1.0||2.0' '1||2' '2009-03-18 15:20:10'
11 104 1 '1.0||2.0' '1||2' '2009-03-18 15:20:10'
10 100 5 '1.0||2.0' '1||2' '2009-03-18 15:20:9'
9 100 2 '1.0||2.0' '1||2' '2009-03-18 15:20:8'
8 103 1 '1.0||2.0' '1||2' '2009-03-18 15:20:7'
3 102 1 '1.0||2.0' '1||2' '2009-03-18 15:20:2'

要求：

取得最新的. 同时去掉mathchid,chipintypeid重复的数据行
如上面有4条 matchid=100 chipintypeid=1的数据，只取最新的，就是ID为12的记录
matchid=104 chipintypeid=1也是一样的


SQL code--1
select t.* from tablea t where changedate = (select max(changedate) from tablea where mathchid = t.mathchid and chipintypeid = t.chipintypeid)

--2
select t.* from tablea t where not exists (select 1 from tablea where mathchid = t.mathchid and chipintypeid = t.chipintypeid and changedate > t.changedate)