不管这个值出现多少次只显示一次。
distinct 字段名1，字段名2 from 表格 order by 字段名1
最好和order by 结合使用。可以提高效率
sql>
sql> create table employees (
2 au_id char(3) not null,
3 au_fname varchar(15) not null,
4 au_lname varchar(15) not null,
5 phone varchar(12) null ,
6 address varchar(20) null ,
7 city varchar(15) null ,
8 state char(2) null ,
9 zip char(5) null
10 );
table created.
sql>
sql> insert into employees values('a01','s','b','111-111-1111','75 st','boston','ny','11111');
1 row created.
sql> insert into employees values('a02','w','h','222-222-2222','2922 rd','boston','co','22222');
1 row created.
sql> insert into employees values('a03','h','h','333-333-3333','3800 ave, #14f','san francisco','ca','33333');
1 row created.
sql> insert into employees values('a04','k','h','444-444-4444','3800 ave, #14f','san francisco','ca','44444');
1 row created.
sql> insert into employees values('a05','c','k','555-555-5555','114 st','new york','ny','55555');
1 row created.
sql> insert into employees values('a06',' ','k','666-666-666','390 mall','palo alto','ca','66666');
1 row created.
sql> insert into employees values('a07','p','o','777-777-7777','1442 st','sarasota','fl','77777');
1 row created.
sql>
sql>
sql> select distinct state
2 from employees;
看过滤重复的sql语句
sql> select * from employee
2 /
id first_name last_name start_dat end_date salary city description
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01 jason martin 25-jul-96 25-jul-06 1234.56 toronto programmer
02 alison mathews 21-mar-76 21-feb-86 6661.78 vancouver tester
03 james smith 12-dec-78 15-mar-90 6544.78 vancouver tester
04 celia rice 24-oct-82 21-apr-99 2344.78 vancouver manager
05 robert black 15-jan-84 08-aug-98 2334.78 vancouver tester
06 linda green 30-jul-87 04-jan-96 4322.78 new york tester
07 david larry 31-dec-90 12-feb-98 7897.78 new york manager
08 james cat 17-sep-96 15-apr-02 1232.78 vancouver tester
8 rows selected.
sql>
sql>
sql> select description from employee
2 /
description
---------------
programmer
tester
tester
manager
tester
tester
manager
tester
8 rows selected.
sql> select distinct description from employee
2 /
description
---------------
programmer
manager
tester
如果要处理多列表的话，字段以“,分开就要以了，如下
sql> select distinct city, state
2 from employees
关于增加了distinct后查询的效率测试
只有增加distinct关键字，oracle必然需要对后面的所有字段进行排序。以前也经常发现由于开发人员对sql不是很理解，在select列表的20多个字段前面添加了distinct，造成查询基本上不可能执行完成，甚至产生ora-7445错误。所以一直向开发人员强调distinct给性能带来的影响。
没想到开发人员在测试一条大的sql的时候，告诉我如果加上了distinct，则查询大概需要4分钟左右可以执行完，如果不加distinct，则查询执行了10多分钟，仍然得不到结果。
首先想到的是可能distinct是在子查询中，由于加上了distinct，将第一步结果集缩小了，导致查询性能提高，结果一看sql，发现distinct居然是在查询的最外层。
由于原始sql太长，而且牵扯的表太多，很难说清楚，这里模拟了一个例子，这个例子由于数据量和sql的复杂程度限制，无法看出二者执行时间上的明显差别。这里从两种情况的逻辑读对比来说明问题。
首先建立模拟环境：
sql> create table t1 as select * from dba_objects
2 where owner = 'sys'
3 and object_type not like '%body'
4 and object_type not like 'java%';
table created.
sql> create table t2 as select * from dba_segments where owner = 'sys';
table created.
sql> create table t3 as select * from dba_indexes where owner = 'sys';
table created.
sql> alter table t1 add constraint pk_t1 primary key (object_name);
table altered.
sql> create index ind_t2_segname on t2(segment_name);
index created.
sql> create index ind_t3_tabname on t3(table_name);
index created.
sql> exec dbms_stats.gather_table_stats(user, 't1', method_opt => 'for all indexed columns size 100', cascade => true)
pl/sql procedure successfully completed.
sql> exec dbms_stats.gather_table_stats(user, 't2', method_opt => 'for all indexed columns size 100', cascade => true)
pl/sql procedure successfully completed.
sql> exec dbms_stats.gather_table_stats(user, 't3', method_opt => 'for all indexed columns size 100', cascade => true)
pl/sql procedure successfully completed.