中国素材网站第一门户_素材_源码_模板_教程_字体免费下载-有材网

oracle distinct 的使用方法

时间:2014-05-12 10:07来源: 作者: 点击:
教程介绍:distinct这个关键字来过滤掉多余的重复记录只保留一条,但往往只用 它来返回不重复记录的条数,而不是用它来返回不重记录的所有值。其原因是distinct只有用二重循环查询来解决,而这样对于一个数据量非常大的站来说,无疑是会直接影响到效率的。 SQL -- crea

   distinct这个关键字来过滤掉多余的重复记录只保留一条,但往往只用 它来返回不重复记录的条数,而不是用它来返回不重记录的所有值。其原因是distinct只有用二重循环查询来解决,而这样对于一个数据量非常大的站来说,无疑是会直接影响到效率的。

  SQL> -- create demo table

  SQL> create table Employee(

  2 ID VARCHAR2(4 BYTE) NOT NULL,

  3 First_Name VARCHAR2(10 BYTE),

  4 Last_Name VARCHAR2(10 BYTE),

  5 Start_Date DATE,

  6 End_Date DATE,

  7 Salary Number(8,2),

  8 City VARCHAR2(10 BYTE),

  9 Description VARCHAR2(15 BYTE)

  10 )

  11 /

  Table created.

  SQL>

  SQL> -- prepare data

  SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

  2 values ('01','Jason', 'Martin', to_date('19960725','YYYYMMDD'), to_date('20060725','YYYYMMDD'), 1234.56, 'Toronto', 'Programmer')

  3 /

  1 row created.

  SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

  2 values('02','Alison', 'Mathews', to_date('19760321','YYYYMMDD'), to_date('19860221','YYYYMMDD'), 2334.78, 'Vancouver','Tester')

  3 /

  1 row created.

  SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

  2 values('03','James', 'Smith', to_date('19781212','YYYYMMDD'), to_date('19900315','YYYYMMDD'), 2334.78, 'Vancouver','Tester')

  3 /

  1 row created.

  SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

  2 values('04','Celia', 'Rice', to_date('19821024','YYYYMMDD'), to_date('19990421','YYYYMMDD'), 2334.78, 'Vancouver','Manager')

  3 /

  1 row created.

  SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

  2 values('05','Robert', 'Black', to_date('19840115','YYYYMMDD'), to_date('19980808','YYYYMMDD'), 2334.78, 'Vancouver','Tester')

  3 /

  1 row created.

  SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

  2 values('06','Linda', 'Green', to_date('19870730','YYYYMMDD'), to_date('19960104','YYYYMMDD'), 2334.78,'New York', 'Tester')

  3 /

  1 row created.

  SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

  2 values('07','David', 'Larry', to_date('19901231','YYYYMMDD'), to_date('19980212','YYYYMMDD'), 2334.78,'New York', 'Manager')

  3 /

  1 row created.

  SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

  2 values('08','James', 'Cat', to_date('19960917','YYYYMMDD'), to_date('20020415','YYYYMMDD'), 2334.78,'Vancouver', 'Tester')

  3 /

  1 row created.

  SQL>

  SQL>

  SQL>

  SQL> -- display data in the table

  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 2334.78 Vancouver Tester

  03 James Smith 12-DEC-78 15-MAR-90 2334.78 Vancouver Tester

  04 Celia Rice 24-OCT-82 21-APR-99 2334.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 2334.78 New York Tester

  07 David Larry 31-DEC-90 12-FEB-98 2334.78 New York Manager

  08 James Cat 17-SEP-96 15-APR-02 2334.78 Vancouver Tester

  8 rows selected.

  SQL>

  SQL>

  SQL>

  SQL>

  SQL>

  SQL> -- Remember that the DISTINCT operator applies to the entire select list.

  SQL>

  SQL> SELECT DISTINCT City, Description FROM Employee;

  CITY DESCRIPTION

  ---------- ---------------

  New York Manager

  Vancouver Tester

  Toronto Programmer

  Vancouver Manager

  New York Tester

  同时与groupy count 使用的用法

  SQL> select Coder

  2 , count(distinct course)

  3 , count(*)

  4 from offerings

  5 group by Coder;

  CODER COUNT(DISTINCTCOURSE) COUNT(*)

  ---------- --------------------- ----------

  1 2 3

  4 2 2

  8 2 2

  11 1 1

  13 2 2

  3 3

  6 rows selected.

        :更多精彩文章请关注栏目。

(教程发布:http://www.youcaiw.com)
顶一下
(0)
0%
踩一下
(0)
0%
------分隔线----------------------------
发表评论
请自觉遵守互联网相关的政策法规,严禁发布色情、暴力、反动的言论。
评价:
表情:
验证码:点击我更换图片