Oralce 去重取第一条数据

问题：在项目中有一张设备检测信息表DEVICE_INFO, 每个设备每天都会产生一条检测信息，现在需要从该表中检索出每个设备的最新检测信息。也就是device_id字段不能重复，消除device_id字段重复的记录，而且要求device_id对应的检测信息test_result是最新的。

 

创建测试环境

在线数据库 http://sqlfiddle.com/

ID	DEVICE_ID	MODIFY_DATE	TEST_RESULT
1	21	2010-4-3	正常
2	21	2010-4-4	异常
3	23	2010-4-4	异常
4	22	2010-4-3	警告
5	22	2010-4-4	正常

 

1. 创建表

-- DROP TABLE DEVICE_INFO_TBL;
CREATE TABLE DEVICE_INFO_TBL
    ("ID" int, "DEVICE_ID" int, "MODIFY_DATE" date, "TEST_RESULT" varchar2(10));

 

2. 插入测试数据

INSERT ALL 
    INTO DEVICE_INFO_TBL ("ID", "DEVICE_ID", "MODIFY_DATE", "TEST_RESULT")
         VALUES (1, 21, to_date('2010-4-3', 'yyyy-MM-dd'), '正常')
    INTO DEVICE_INFO_TBL ("ID", "DEVICE_ID", "MODIFY_DATE", "TEST_RESULT")
         VALUES (2, 21, to_date('2010-4-4', 'yyyy-MM-dd'), '异常')
    INTO DEVICE_INFO_TBL ("ID", "DEVICE_ID", "MODIFY_DATE", "TEST_RESULT")
         VALUES (3, 23, to_date('2010-4-4', 'yyyy-MM-dd'), '异常')
    INTO DEVICE_INFO_TBL ("ID", "DEVICE_ID", "MODIFY_DATE", "TEST_RESULT")
         VALUES (4, 22, to_date('2010-4-3', 'yyyy-MM-dd'), '警告')
    INTO DEVICE_INFO_TBL ("ID", "DEVICE_ID", "MODIFY_DATE", "TEST_RESULT")
         VALUES (5, 22, to_date('2010-4-4', 'yyyy-MM-dd'), '正常')
SELECT * FROM dual;
commit;

 

解决思路：

用Oracle的row_number() over()函数来解决该问题。

 

解决过程：

查看表中的重复记录

select t.id, t.device_id, t.modify_date, t.test_result
  from device_info_tbl t

 

标记重复的记录

select t.id,
       t.device_id,
       t.modify_date,
       t.test_result,
       row_number() over(PARTITION BY device_id ORDER BY modify_date desc) row_flag
  from device_info_tbl t;

 

过滤重复数据，取最新记录

select id, device_id, modify_date, test_result
  from (select t.id,
               t.device_id,
               t.modify_date,
               t.test_result,
               row_number() over(PARTITION BY device_id ORDER BY modify_date desc) row_flag
          from device_info_tbl t)
 where row_flag = 1;

 

总结

row_number() over(PARTITION BY col1 ORDER BY col2 desc)表示根据 col1 分组，在分组内部根据 col2倒序排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的)。

与rownum的区别在于：使用 rownum 进行排序的时候是先对结果集加入伪列rownum然后再进行排序，而此函数在包含排序从句后是先排序再计算行号码。

• row_number()和rownum差不多，功能更强一点（可以在各个分组内从1开时排序）。
• rank()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内）。
• dense_rank()是连续排序，有两个第二名时仍然跟着第三名。相比之下row_number是没有重复值的 。

 

参考：

[http://blog.csdn.net/nux_123/article/details/45037719](