oracle基础积累-使用ROW_NUMBER() OVER函数去重

场景:
       去重.
环境:
        Oracle Database 11g; PL/SQL Developer
1.去重
    思路:ROW_NUMBER() OVER(PARTITION BY 需要去重字段 ORDER BY 选择排序字段 DESC) 
    为指定的去重字段,标上行号,如果有重复的,选中行号为1的就可以.

SELECT *
  FROM (SELECT T.*,
               ROW_NUMBER() OVER(PARTITION BY T.SENSOR_ID ORDER BY T.DATA_DATE DESC) AS NM
          FROM SENSOR_COLLECT_DATA T) TT
 WHERE TT.NM = 1 ;

2.去重前结果
   
2.去重后结果
   
4.附加,找到重复的字段值

SELECT *
  FROM SENSOR_COLLECT_DATA
 WHERE SENSOR_ID IN (SELECT SENSOR_ID
                       FROM SENSOR_COLLECT_DATA
                      GROUP BY SENSOR_ID
                     HAVING COUNT(SENSOR_ID) > 1);

5.附加,建表语句

create table SENSOR_COLLECT_DATA
(
  sensor_id         NUMBER(16) not null,
  data_date       DATE not null,
  data_type       NUMBER(2) not null,
  region          VARCHAR2(16) not null,
  s1       NUMBER(6,3),        
  s2       NUMBER(6,3),       
  s3       NUMBER(6,3)
);
comment on table SENSOR_COLLECT_DATA
  is '传感器采集数据';
comment on column SENSOR_COLLECT_DATA.sensor_id
  is '数据id实体唯一标识';
comment on column SENSOR_COLLECT_DATA.data_date
  is '数据日期';
comment on column SENSOR_COLLECT_DATA.data_type
  is '数据类型(1:温度、2:湿度)';
comment on column SENSOR_COLLECT_DATA.region
  is '传感器安装区域';
comment on column SENSOR_COLLECT_DATA.s1
  is '传感器采集的值1';
comment on column SENSOR_COLLECT_DATA.s2
  is '传感器采集的值2';
comment on column SENSOR_COLLECT_DATA.s3
  is '传感器采集的值3';

以上,感谢.