在Oracle中将一张表进行去重处理，只留下最新记录

[gaohe198830]

表结构如图，期望结果是将获取A列中张三、李四、王五的最新记录，C字段为更新日期，求简洁、高效的执行SQL

我自己写的SQL是
select a.*，b* from table a,(select A,max(c) from table group by A) b where a.A=b.A and a.C=b.C
可以查询到结果，但是由于原库数据有66万多记录，包含的字段也远比示例图复杂，执行效率很低

[lk19900723]

C字段是否添加了索引, 尝试吧select A,max(c) from table group by A 单独建一张临时表,子查询实在影响效能,然后再和主表关联!

[lk19900723]

select a.* from table a,(select A, max(c) over(order by c) maxc from table ）b where and b.mac=a.C; select a.* from table a where a.C = (Select Max(c) From table b Where a.A = b.A)

[Jerry_Li-CN]

按姓名和时间分组排序，加上序号，将序号不等于1的都删掉

[monei_]

WITH c_test AS(
     SELECT '张三' A,TRUNC(dbms_random.value(10,100)) D,TRUNC(dbms_random.value(10,100)) B,20130411 C FROM dual 
     UNION
     SELECT '张三' A,TRUNC(dbms_random.value(10,100)) D,TRUNC(dbms_random.value(10,100)) B,20130511 C FROM dual 
     UNION
     SELECT '张三' A,TRUNC(dbms_random.value(10,100)) D,TRUNC(dbms_random.value(10,100)) B,20130311 C FROM dual 
     UNION
     SELECT '李四' A,TRUNC(dbms_random.value(10,100)) D,TRUNC(dbms_random.value(10,100)) B,20130611 C FROM dual 
     UNION
     SELECT '李四' A,TRUNC(dbms_random.value(10,100)) D,TRUNC(dbms_random.value(10,100)) B,20130311 C FROM dual 
     UNION
     SELECT '李四' A,TRUNC(dbms_random.value(10,100)) D,TRUNC(dbms_random.value(10,100)) B,20130411 C FROM dual 
     UNION
     SELECT '王五' A,TRUNC(dbms_random.value(10,100)) D,TRUNC(dbms_random.value(10,100)) B,20130311 C FROM dual 
     UNION
     SELECT '王五' A,TRUNC(dbms_random.value(10,100)) D,TRUNC(dbms_random.value(10,100)) B,20140211 C FROM dual 
)
SELECT *
  FROM c_test t
 WHERE EXISTS (SELECT 1
                 FROM (SELECT t.a,MAX(t.c) c
                         FROM c_test t
                        GROUP BY t.a) b
                WHERE b.a = t.a
                  AND t.c = b.c);

[小灰狼W]

select * from a t where not exists(select 1 from a where a=t.a and c>t.c);

[小灰狼W]

创建a和c字段的组合索引

[Gospelanswer]

select a,d,b,max(c) from table group by a,d,b

[小伙要自信]

引用 7 楼 Gospelanswer 的回复:

select a,d,b,max(c) from table group by a,d,b

我的直接想法，同7楼

[bw555]

5楼正解， 7楼结果应该是不正确的 例：按照ADB分组 结果中 张三 65 73 张三 66 74 张三 69 77 在结果中应该都会出现，而按楼主的要求，应该只要最新的一条

[yiquliushang]

引用 9 楼 bw555 的回复:

5楼正解， 7楼结果应该是不正确的 例：按照ADB分组 结果中 张三 65 73 张三 66 74 张三 69 77 在结果中应该都会出现，而按楼主的要求，应该只要最新的一条

max(c)

[yiyongle]

你试试这样行不行 select a,d,b,max(c) from table group by a

[sych888]

分析函数 row_number() over(parititon by ..... order by .... desc)

[actonhb]

支持11楼的解法

[bw555]

引用 11 楼 u014530814 的回复:

你试试这样行不行 select a,d,b,max(c) from table group by a

引用 13 楼 sdfgsdgdsfgdfgdsfghf 的回复:

支持11楼的解法

11楼的这写法，语法都不能通过，竟然还有人支持

[不写代码的钦]

如12楼所说，分析函数。 select * from (select a.*,row_number() over(partition by a.a order by a.c desc) rn) where rn=1;

[ning_wnn2014]

select * from q t where not exists(select 1 from q where A=t.A and C>t.C);

[ffshao]

WITH c_test AS( SELECT '张三' A,TRUNC(dbms_random.value(10,100)) D,TRUNC(dbms_random.value(10,100)) B,20130411 C FROM dual UNION SELECT '张三' A,TRUNC(dbms_random.value(10,100)) D,TRUNC(dbms_random.value(10,100)) B,20130511 C FROM dual UNION SELECT '张三' A,TRUNC(dbms_random.value(10,100)) D,TRUNC(dbms_random.value(10,100)) B,20130311 C FROM dual UNION SELECT '李四' A,TRUNC(dbms_random.value(10,100)) D,TRUNC(dbms_random.value(10,100)) B,20130611 C FROM dual UNION SELECT '李四' A,TRUNC(dbms_random.value(10,100)) D,TRUNC(dbms_random.value(10,100)) B,20130311 C FROM dual UNION SELECT '李四' A,TRUNC(dbms_random.value(10,100)) D,TRUNC(dbms_random.value(10,100)) B,20130411 C FROM dual UNION SELECT '王五' A,TRUNC(dbms_random.value(10,100)) D,TRUNC(dbms_random.value(10,100)) B,20130311 C FROM dual UNION SELECT '王五' A,TRUNC(dbms_random.value(10,100)) D,TRUNC(dbms_random.value(10,100)) B,20140211 C FROM dual ) select a.* from c_test a, (select s.a name,max(c) maxtime from c_test s group by s.a) b where a.A=b.name and a.C=b.maxtime;