oracle基础积累-常用知识二

场景:
        常用基础查询
环境:
        ORACLE DATABASE 11G; PL/SQL DEVELOPER
1.CASE WHEN THEN END 使用

SELECT AA.SENSOR_ID,
       CASE
         WHEN AA.REGION = 2019 THEN
          2018
         ELSE
          2017
       END AS NEW_REGION
  FROM SENSOR_COLLECT_DATA AA

2. GROUP BY 分类

SELECT DISTINCT AA.REGION, COUNT(SENSOR_ID) AS SENSOR_ID1
  FROM SENSOR_COLLECT_DATA AA
 GROUP BY AA.REGION

3. GROUP BY 分类排序

SELECT DISTINCT AA.REGION, COUNT(SENSOR_ID) AS SENSOR_ID1
  FROM SENSOR_COLLECT_DATA AA
 GROUP BY AA.REGION ORDER BY AA.REGION DESC

4. NOT EXISTS

SELECT *
  FROM SENSOR_COLLECT_DATA AA
 WHERE NOT EXISTS (SELECT 1
                FROM SENSOR_COLLECT_A BB
               WHERE AA.SENSOR_ID = BB.SENSOR_ID);

5. EXISTS

SELECT *
  FROM SENSOR_COLLECT_DATA AA
 WHERE EXISTS(SELECT 1
                FROM SENSOR_COLLECT_A BB
               WHERE AA.SENSOR_ID = BB.SENSOR_ID);

6. HAVING

SELECT AA.REGION, COUNT(SENSOR_ID) AS SENSOR_ID_NUM
  FROM SENSOR_COLLECT_DATA AA
 GROUP BY AA.REGION
HAVING COUNT(1) > 1

7. 去重

DELETE FROM  SENSOR_COLLECT_DATA
 WHERE SENSOR_ID IN (SELECT SENSOR_ID
                       FROM SENSOR_COLLECT_DATA 
                      GROUP BY SENSOR_ID
                     HAVING COUNT(1) > 1)  
   AND ROWID NOT IN (SELECT MIN(ROWID)
                       FROM SENSOR_COLLECT_DATA
                      GROUP BY SENSOR_ID
                     HAVING COUNT(1) > 1)

7.1找到重复的id

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

7.2 找到行号最小的

SELECT MIN(ROWID)
  FROM SENSOR_COLLECT_DATA
 GROUP BY SENSOR_ID
HAVING COUNT(1) > 1;

8.测试数据
  
9.1附建表语句一

create table SENSOR_COLLECT_DATA
(
  sensor_id         NUMBER(16) 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.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';

9.2附建表语句二

create table SENSOR_COLLECT_A
(
  sensor_id         NUMBER(16) not null,
  region          VARCHAR2(16) not null,
  s1       NUMBER(6,3)        
);
comment on table SENSOR_COLLECT_A
  is '传感器采集数据';
comment on column SENSOR_COLLECT_A.sensor_id
  is '数据id实体唯一标识';
comment on column SENSOR_COLLECT_A.region
  is '传感器安装区域';
comment on column SENSOR_COLLECT_A.s1
  is '传感器采集的值1';

9.3附建表语句三

create table SENSOR_COLLECT_B
(
  sensor_id         NUMBER(16) not null,
  region          VARCHAR2(16) not null,
  s1       NUMBER(6,3)       
);
comment on table SENSOR_COLLECT_B
  is '传感器采集数据';
comment on column SENSOR_COLLECT_B.sensor_id
  is '数据id实体唯一标识';
comment on column SENSOR_COLLECT_B.region
  is '传感器安装区域';
comment on column SENSOR_COLLECT_B.s1
  is '传感器采集的值1';

以上,感谢.