distinct oracle 查询唯一值_oracle 一次取所有列的distinct 值 - CSDN
精华内容
参与话题
  • Oracledistinct关键字

    2020-03-25 14:46:53
    distinct关键字用于从查询的结果集中筛选出唯一值的记录。 我们通过示例来介绍distinct关键字的用法。 一、生成测试数据 用以下SQL创建超女基本信息表(T_GIRL),插入一些测试数据。 create table T_GIRL ( id ...

    distinct关键字用于从查询的结果集中筛选出唯一值的记录。

    我们通过示例来介绍distinct关键字的用法。

    一、生成测试数据

    用以下SQL创建超女基本信息表(T_GIRL),插入一些测试数据。

    create table T_GIRL
    (
      id        char(4)         not null,   -- 编号
      name      varchar2(30)    not null,   -- 姓名
      yz        varchar2(20)        null,   -- 颜值
      sc        varchar2(20)        null,   -- 身材
      weight    number(4,1)     not null,   -- 体重
      height    number(3)       not null,   -- 身高
      birthday  date            not null,   -- 出生时间
      memo      varchar2(1000)      null    -- 备注
    );
    insert into T_GIRL(id,name,yz,birthday,sc,weight,height,memo)
      values('0101','西施','漂亮',to_date('2000-01-01 01:12:35','yyyy-mm-dd hh24:mi:ss'),
             '火辣',48.5,170,'这是一个非常漂亮姑娘,老公是夫差,男朋友是范蠡。');
    insert into T_GIRL(id,name,yz,birthday,sc,weight,height,memo)
      values('0102','貂禅','漂亮',to_date('1997-08-02 12:20:38','yyyy-mm-dd hh24:mi:ss'),
             '苗条',45.2,168,'王允真不是男人,干不过董卓就把美人往火坑里推,千古罪人啊。');
    insert into T_GIRL(id,name,yz,birthday,sc,weight,height,memo)
      values('0103','妲已','漂亮',to_date('1998-03-03 10:50:33','yyyy-mm-dd hh24:mi:ss'),
             '火辣',53.6,172,'如果商真的因我而亡,您们男人做什么去了?');
    insert into T_GIRL(id,name,yz,birthday,sc,weight,height,memo)
      values('0104','芙蓉姐姐','猪扒',to_date('1980-05-05 10:11:55','yyyy-mm-dd hh24:mi:ss'),
             '膘肥体壮',85.8,166,'如果不努力学习技术,将来就会娶个芙蓉姐姐,哼哼。');
    

    二、distinct示例

    从T_GIRL表中查出全部的颜值信息:

    select yz from T_GIRL;
    

    在这里插入图片描述

    如果我想知道颜值有哪些取值,所以希望从结果集中去掉重复的记录,加上distinct关键字,位置在select和字段列表之间。

    在这里插入图片描述

    distinct是从结果集中筛选出唯一值的记录,上面示例的结果集只有一个字段,也可以多个字段,多个字段组合的值唯一。

    未加distinct关键字。

    select yz,sc from T_GIRL; 
    

    在这里插入图片描述

    加了distinct关键字。

    select distinct yz,sc from T_GIRL; 
    

    在这里插入图片描述

    三、版权声明

    C语言技术网原创文章,转载请说明文章的来源、作者和原文的链接。
    来源:C语言技术网(www.freecplus.net)
    作者:码农有道

    如果文章有错别字,或者内容有错误,或其他的建议和意见,请您留言指正,非常感谢!!!

    展开全文
  • Oracle性能分析12:对象统计信息

    千次阅读 2014-10-29 09:59:58
    对象统计信息描述数据是如何在数据库中存储的,查询优化器使用这些统计信息来做出正确的决定。Oracle中有三种类型的对象统计信息:表统计、列统计和索引统计。而在每种类型中,有细分为:表或索引级别的统计、分区...

    对象统计信息描述数据是如何在数据库中存储的,查询优化器使用这些统计信息来做出正确的决定。Oracle中有三种类型的对象统计信息:表统计、列统计和索引统计。而在每种类型中,有细分为:表或索引级别的统计、分区级别统计和子分区级别的统计,后面两种只有在对象被分区和具有子分区的情况下才可用。

    统计信息相关视图

    表统计信息

    表/索引级别的统计

    user_tab_statistics
    user_tables

    分区级别的统计

    user_tab_statistics
    user_tab_partitions

    子分区级别统计

    user_tab_statistics
    user_tab_subpartitions

    列统计信息

    表/索引级别的统计

    user_tab_col_statistics
    user_tab_histograms

    分区级别的统计

    user_part_col_statistics
    user_part_histograms

    子分区级别统计

    user_subpart_col_statistics
    user_subpart_histograms

    索引统计信息

    表/索引级别的统计

    user_ind_statistics
    user_indexes

    分区级别的统计

    user_ind_statistics
    user_ind_partitions

    子分区级别统计

    user_ind_statistics
    user_ind_subpartitions

    创建测试表

    这里将创建测试表T用于后面对统计信息的说明。

    创建测试表

    create table test as 
    select rownum as id,
           round(dbms_random.normal * 1000) as val1,
           100 + round(ln(rownum / 3.25 + 2)) as val2,
           100 + round(ln(rownum / 3.25 + 2)) as val3,
           dbms_random.string('p', 250) as pad
      from all_objects
     where rownum <= 1000
     order by dbms_random.value

    上面的语句创建了一个1000行的表,然后我们将val1列中的负值清空:

    update test set val1 = null where val1 < 0;

    为测试表添加主键和索引

    alter table test add constraint test_pk primary key (id);
    create index test_val1 on test (val1);
    create index test_val2 on test (val2);

    为测试表收集统计信息

    begin
      dbms_stats.gather_table_stats(ownname          => user,
                                    tabname          => 'TEST',
                                    estimate_percent => 100,
                                    method_opt       => 'for all columns size skewonly',
                                    cascade          => TRUE);
    end;

    表统计信息

    下面是表统计信息中的关键字段:

    select num_rows, blocks, empty_blocks, avg_space, chain_cnt, avg_row_len
      from user_tab_statistics
     where table_name = 'TEST';
     
    NUM_ROWS	BLOCKS	EMPTY_BLOCKS	AVG_SPACE	CHAIN_CNT	AVG_ROW_LEN
    ----------------------------------------------------------------------------------
    1000		39		0				0			0			265

    下面是对字段含义的解释:

    1)num_rows:表中数据的行数;
    2)blocks:高水位线以下的数据块个数(高水位线详见“Oracle性能分析4:数据访问方法之全扫描”http://blog.csdn.net/tomato__/article/details/38981425);
    3)empty_blocks:高水位线以上的数据块个数,由于dbms_stats不计算该值,因此为0;
    4)avg_space:表里数据块的平均空闲空间(字节),由于dbms_stats不计算该值,因此为0;
    5)chain_cnt:涉及行链接和行迁移的总行数,由于dbms_stats不计算该值,因此为0(详见“Oracle行迁移和行链接”http://blog.csdn.net/tomato__/article/details/40146573);
    6)avg_row_len:表中平均每个记录的长度(字节)。

    列统计信息

    下面是列统计信息的最重要的统计信息字段:

    select column_name,
           num_distinct,
           low_value,
           high_value,
           density,
           num_nulls,
           avg_col_len,
           histogram,
           num_buckets
      from user_tab_col_statistics
     where table_name = 'TEST';

    下面是对这些字段的解释:
     1)num_distinct:该列中不同值的数量;
     2)low_value:该列的最小值。显示为内部存储的格式,对于字符串列只存储前32字节;
     3)high_value:该列的最大值。显示为内部存储的格式,对于字符串列只存储前32字节;
     4)density:0到1之间的一个小数。接近0表示对于列的过滤操作能去掉大多数行。接近1表示对于该列的过滤操作起不到什么作用。
     如果没有直方图,该值的计算方法为:density=1/num_distinct。
     如果有直方图,则根据不同的直方图类型有不同的计算方法。
     5)num_nulls:该列中存储的NULL的总数;
     6)avg_col_len:平均列大小,以字节表示;
     7)histogram:表明是否有直方图统计信息,值包括:NONE(没有)、FREQUENCY(频率类型)和HEIGHT BALANCED(平均分布类型);
     8)num_buckets:直方图里的bucket的数量,最小为1,最大为254。
    注:low_value和high_value表示为内部存储的格式,下面的存储过程可以得到test表的所有列的最大最小值:

    declare
      l_val1 test.val1%type;
    begin
      for v in (select low_value, high_value
                  from user_tab_col_statistics
                 where table_name = 'TEST') loop
        dbms_stats.convert_raw_value(v.low_value, l_val1);
        dbms_output.put_line('low value : ' || l_val1);
        dbms_stats.convert_raw_value(v.high_value, l_val1);
        dbms_output.put_line('low value : ' || l_val1);
      end loop;
    end;

    直方图

    查询优化器需要找到满足条件的数据行数,如果列的数据均匀分布的,则很容易根据最小值、最大值和唯一值总数就可以计算得到,这些信息在列统计信息中就可以得到。但如果数据不是均匀分布的,查询优化器则需要额外的信息才能做出正确估算。
    这些查询优化器需要的关于数据不均匀分布的额外信息叫做直方图,存在两种类型的直方图:频度直方图(frequency histogram)和等高直方图(height-balanced histogram)。

    频度直方图

    频度直方图的本质特性如下:
     1)桶数(即分类数)等于唯一值总数。对于每个桶来说,视图user_tab_histograms有一行数据与之对应;
     2)列endpoint_value提供该值本身。该列为number类型,应此非数字类型的列必须要进行转换,只取前六个字节。这意味着直方图中存储的值的分布是基于列的前面部分,因而,固定前缀的字符串会使直方图的分布严重不均衡;
     3)列endpoint_number是取值的累积出现次数,当前的endpoint_number减去上一个endpoint_number,就是当前行这个值的出现次数。
    通过下面的方式就可以得到列val2的频次:

    select column_name,
           endpoint_value,
           endpoint_number,
           endpoint_number - lag(endpoint_number, 1, 0) over(order by endpoint_number) as frequency
      from user_tab_histograms
     where table_name = 'TEST'
       and column_name = 'VAL2'
     order by endpoint_number
    
    COLUMN_NAME	ENDPOINT_VALUE	ENDPOINT_NUMBER	FREQUENCY
    -------------------------------------------------------
    VAL2				101				8			8
    VAL2				102				33			25
    VAL2				103				101			68
    VAL2				104				286			185
    VAL2				105				788			502
    VAL2				106				1000		212

    下面用test表作为一个例子说明优化器怎样利用频度直方图精确估算查询返回的行数:

    explain plan set statement_id '101' for select * from test where val2 = 101;
    explain plan set statement_id '102' for select * from test where val2 = 102;
    explain plan set statement_id '103' for select * from test where val2 = 103;
    explain plan set statement_id '104' for select * from test where val2 = 104;
    explain plan set statement_id '105' for select * from test where val2 = 105;
    explain plan set statement_id '106' for select * from test where val2 = 106;

    然后我们查看执行计划对返回行数的估算:

    select statement_id,cardinality from plan_table where id = 0;
    
    STATEMENT_ID	CARDINALITY
    ----------------------------------
    	101			8
    	102			25
    	103			68
    	104			185
    	105			502
    	106			212

    等高直方图

    当一列的唯一值数量总是大于桶的允许最大数量(254)时,就不能使用频度直方图了,这是就只能使用等高直方图了。
    等高直方图的主要特征如下:
     1)桶数少于唯一值总数。除非被压缩,否则对应于每个桶,视图user_tab_histograms里都有一个包含端点号(endpoint number)的行与之对应,端点号0表明最小取值;
     2)端点值(endpoint_value)就是列的数值。因为该列是number类型,非数字类型必须进行转换,此值仅取前六个字节;
     3)endpoint_number列给出了桶号;
     4)直方图不存储一个取值的频度。
    等高直方图只存储列值属于某一个桶,如果有两个列值位于同一个桶,则其中一个将被忽略(压缩),这样的统计就可能导致估算不准确。在实践中,等高直方图不但可能导致错误的估算,还可能引起查询优化器估值的不稳定。

    索引统计信息

    下面的查询可以得到索引统计信息:

    select index_name,
           blevel,
           leaf_blocks,
           distinct_keys,
           num_rows,
           clustering_factor,
           avg_leaf_blocks_per_key,
           avg_data_blocks_per_key
      from user_ind_statistics
     where table_name = 'TEST';

    主要字段的含义如下:
     1)blevel:为了访问叶子块而需要读取的分支块的数量,包括根块;
     2)leaf_blocks:索引中的叶子块数;
     3)distinct_keys:索引中的唯一键值总数;
     4)num_rows:索引中的键值数;
     5)clustering_factor:见“Oracle性能分析8:使用索引”http://blog.csdn.net/tomato__/article/details/39294655;
     6)avg_leaf_blocks_per_key:存放一个键值的平均叶子块数,公式如下;
     avg_leaf_blocks_per_key = leaf_blocks/distinct_keys
     7)avg_data_blocks_per_key:表中单个键引用的平均数据块数,公式如下:
     avg_data_blocks_per_key = clustering_factor/distinct_keys


    展开全文
  • Oracle去重函数distinct

    千次阅读 2019-11-27 10:40:24
    distinct用于返回唯一不同的。 表table1: id value 1 a 1 a 2 b 3 b 4 c 5 c 表table2: username password tom s123456 toms 123456 表table3: ...

    distinct用于返回唯一不同的值。

    表table1:

    id value
    1 a
    1 a
    2 b
    3 b
    4 c
    5 c

    表table2:

    username password
    tom s123456
    toms 123456

     

    表table3:

    username phone
    tom 10086
    jack 10010

    作用于单列

    select distinct value from table1

    执行结果如下:

    value
    a
    b
    c

     

    作用于多列

    例1:

    select distinct id, name from table1

    执行结果如下:

    id value
    1 a
    2 b
    3 b
    4 c
    5 c

    通过这个例子可以看出,多列去重是根据写在distinct后面的各个字段来去重的(当这几个字段所有的列的值都相同时才会被认为是重复数据)。

    例2:

    select distinct username, password from table2

    执行结果如下:

    username password
    tom s123456
    toms 123456

    执行结果为两行,这说明distinct是分别作用了username和password列,而不是把这两列的值“拼接”后再去重的。

     

    多表查询

    select distinct o.username from table2 o , table3 t  where  o.username = t.username ;

    执行结果如下:

    username
    tom

     

    此外还需要注意一下几点:

    1.distinct必须放在开头,如果不放在开头会报错

    select id, distinct value from table1;   --会提示错误

    2.多表查询时,如果表中有相同字段名,需指定是那张表的字段

    select distinct username from table2 o , table3 t  where  o.username = t.username    --会提示错误

    3.COUNT统计单字段

    select count(distinct value) from table1;  --表中value去重后的数目

    4.COUNT统计多字段

    count是不能统计多个字段的,可以使用嵌套查询解决:

    select count(*) from (select distinct username, password from table2) as t2;

     如果这样查询:select count(distinct value, id) from table1;会报错

    5.显示字段

    显示的字段只能是distinct指定的字段

    展开全文
  • Oracle三种去重的方法 distinct group by rowid 分组函数:作用于一组数据,并对一组数据返回一个 常见的分组函数有: count 返回找到的记录数 min 返回一个数字列或计算列的最小值 max 返回一个数字列或计算列...

    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;
    
    1. 同时查询分组函数和非分组函数,并且必须将分组函数以外的所有的列(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结合maxmin函数快速去重

    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结合maxmin函数快速删除重复数据

    -- 使用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函数,类似于Oraclerowidmax
    SQL Server中也有First_ValueLast_Value函数,可以用来实现。

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

    展开全文
  • Oracledistinct的用法实例 摘要: 此外,distinct 会对返回的结果集进行排序 所以会大大影响查询效率,大数据集时比较明显 。所以,最好和order by 结合使用,可以提高效率 。 select distinct a,b,c from t...
  • oracle中SELECT DISTINCT 的非常规用法

    千次阅读 2017-02-09 17:03:53
    新年开工五天,连加了三天的班,这酸爽,...关键词 DISTINCT 用于返回唯一不同的。”-------来自w3cschool。 比如我遇到的问题:表内有字段名为“类型”,表内有很多条记录,我想查找出来这些记录里到底属于哪些类
  • sql select 返回唯一不同的

    千次阅读 2013-08-21 10:17:57
    在sql 中关键词 DISTINCT 用于返回唯一不同的 使用语法 select distinct 列名 from 表名 例表Users select * from Users select distinct Name from Users 如果需要两列以上数据完全相同返回唯一的不同否则...
  • 例:1SELECT DISTINCT * FROM TABLE第二种: 两条记录之间之后只有部分字段的是有重复的,但是表存在主键或者唯一性ID。如果是这种情况的话用DISTINCT是过滤不了的,这就要用到主键id的唯一性特点及group ...
  • 比如我想用一条语句查询得到ID不重复的所有数据,可以有多种方式,列举最常用的两种:  .使用distinct去掉多余的重复记录:[select distinct 字段名 from 表名] select distinct goodsId from po_find_treasure_...
  • 问题描述:将数据从中间表往业务表插入时,提示唯一键重复##...上面的数据执行提示错误,我们选中后半部分查询,发现数据正常,code是唯一的 1 Create table table3 as select distinct code from table2; 2 Insert ...
  • SQL 查询语句 distinct用法总结

    千次阅读 2012-02-16 14:47:42
    distinct的只显示一次重复出更的。 不过这个出现多少次只显示一次。 select distinct 字段名1,字段名2 from 表格 order by 字段名1 distinct 字段名1 意思是只显示一次字段名1显示的是第一次出现的。 最好和...
  • oracle数据库,情景:表(客户-业务员)中数据都不完全重复,但是可能多条数据其中某些字段重复。我想要取得表中符合条件的记录,但是这些记录中的客户id和客户名称发生重复的话只能留一个。如上图我想找出“冯冬梅”...
  • 然后使用Yahoo英文版(Google要是能用或许就没有Yahoo什么事了),搜索到了Stackoverflow上的一篇文章,受到启发,然后使用嵌套一层子查询的方法实现了去重后的连接。本来想贴上原文链接,结果找了半...
  • oracle 多个字段 去重

    千次阅读 2012-09-25 17:38:41
    select distinct(字段一||字段二||字段三)   用“||”连接就行
  • Count distinct是SQL分析时的祸根,因此它是我第一篇博客的不二选择。 首先:如果你有一个大的且能够容忍不精确的数据集,那像HyperLogLog这样的概率计数器应该是你最好的选择。(我们会在以后的博客中谈到...
  • 多重left join去重

    万次阅读 2016-11-30 11:56:14
    在使用mysql时,有时需要查询出某个字段不重复的记录,虽然mysql提供有distinct这个关键字来过滤掉多余的重复记录只保留一条,但往往只 用它来返回不重复记录的条数,而不是用它来返回不重记录的所有。其原因是...
  • 参考:http://heipark.javaeye.com/blog/378540 http://heipark.javaeye.com/blog/378577 DetachedCriteria dc = DetachedCriteria .forClass(DigitalDictionaryInfo.class); dc.addOrder(Order.des
  • Oracle distince 用法和删除重复数据

    千次阅读 2011-01-20 10:13:00
     转自:http://blog.tianya.cn/blogger/post_read.asp?BlogID=144699&PostID=14525538<br /> oracle distinct语句的用法 创建视图时过滤数据  distinct的只显示一次重复出更的。  不过这个...
  • oracle去重(部分和全部)

    千次阅读 2014-03-27 21:38:08
    Distinct,用distinct关键字只能过滤查询字段中所有记录相同的(记录集相同) (1)取一个字段,去除重复项。假设要id这个字段的:  select distinct name from t1; --能消除重复记录,缺点:但只能取一个字段...
  • mysql唯一查询

    千次阅读 2019-06-27 02:01:11
    在使用MySQL时,有时需要查询出某个字段不重复的记录,虽然mysql提供 有distinct这个关键字来过滤掉多余的重复记录只保留一条,但往往只用它来返回不重复记录的条数,而不是用它来返回不重记录的所有。其原因是 ...
1 2 3 4 5 ... 20
收藏数 24,255
精华内容 9,702
关键字:

distinct oracle 查询唯一值