oracle数据库基本语句【oracle,distinct,的使用方法】

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.

注：更多精彩文章请关注三联编程教程栏目。