Oracle 根据某个字段对查询结果去重 三种方法详述

Oracle三种去重的方法

• distinct
• group by
• rowid

分组函数：作用于一组数据，并对一组数据返回一个值

常见的分组函数有：
count 返回找到的记录数
min 返回一个数字列或计算列的最小值
max 返回一个数字列或计算列的最大值
sum 返回一个数字列或计算列总和
avg 返回一个数字列或计算列的平均值

一、distinct

distinct 单列

select distinct(a) from tableA;

distinct 多列

--会报错，distinct 必须放在 select 语句的最前方
select a, distinct b,c from tableA;

select distinct a,b,c from tableA;

多列的时候，上述语句是针对abc的组合来distinct的，列出的是abc的所有不同组合，相当于下列语句

select a,b,c from tableA group by a,b,c;

那么，如果想要分别查询a,b,c三个字段的distinct值怎么办？一开始可能只想到下列三个语句

select distinct a from tableA;
select distinct b from tableA;
select distinct c from tableA;

麻烦，如何合并成一个语句呢，使用union解决，后面拼接的字符串是为了标识这个值属于哪个字段：

select distinct(a) || ' a' from tableA
union all
select distinct(b) || ' b' from tableA
union all
select distinct(c) || ' c' from tableA

二、group by

只查询非分组函数，不需要使用group by；

select Name, Gender from dba_data_files;

只要查询了分组函数，就需要使用group by：

1. 只查询分组函数的话，group by的子句里用到的列名Name 和 Gender，不需要写在select里；但select里出现别的列就会报错；

select sum(Age) from dba_data_files group by Name, Gender;

-- 但 select 里出现别的列就会报错,例：
select Weight, sum(Age) from dba_data_files group by Name, Gender;

2. 同时查询分组函数和非分组函数，并且必须将分组函数以外的所有的列（Name 和 Gender），都写到group by里，少一个都不行；

select Name, Gender, sum(Age) from dba_data_files group by Name, Gender;

不管select是否使用了where子句，都可以使用group by子句

三、rowid

rowid简介

rowid是一个用来唯一标记表中行的伪列。它是物理表中行数据的内部地址，包含两个地址，其一为指向数据表中包含该行的块所存放数据文件的地址，另一个是可以直接定位到数据行自身的这一行在数据块中的地址。
除了在同一聚簇中可能不唯一外，每条记录的rowid是唯一的。可以理解成rowid就是唯一的。

rowid解析

从Oracle 8i开始使用扩展rowid标识行物理地址
扩展rowid使用base64编码行的物理地址，编码字符包含A-Z, a-z, 0-9, +, 和/。
扩展rowid由四部分组成：OOOOOOOFFFBBBBBBRRR:
rowid包含如下内容：
①：对象所在的数据文件号
②：对象所在的块号
③：对象所在行在块内的位置
④：对象号
其中：
OOOOOO：数据对象编号（6位显示）
FFF：相关数据文件编号（3位显示）
BBBBBB：数据块编号（6位显示）
RRR：数据块中行编号（3位显示）

rowid示例

利用rowid结合max和min函数快速去重

select t.* from bill t where t.rowid = (select max(s.rowid) from bill s 
where t.FLAG = s.FLAG
  and s.btype = 1
  and s.CODE in ('71131702422','71130427253','71134427859')
);

利用rowid结合max和min函数快速删除重复数据

-- 使用min函数的时候用大于号>
delete t.* from bill t where t.rowid < (select max(s.rowid) from bill s 
where t.FLAG = s.FLAG
  and s.btype = 1
  and s.CODE in ('71131702422','71130427253','71134427859')
);

本文参考链接1.

Mysql里，可以用Max函数，类似于Oracle的rowid的max；
SQL Server中也有First_Value和Last_Value函数，可以用来实现。

注：group by 必须放在 order by 和 limit之前，不然会报错