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> * 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(*)
———- ——————— ———-
本文来源gaodai$ma#com搞$$代**码网 1 2 3
4 2 2
8 2 2
11 1 1
13 2 2
3 3

6 rows selected.

喜欢 (0)赏

[搞代码]

分享 (0)