2. 分組查詢&amp;&amp;子查詢

2024-07-21 02:52:55

字體：大中小

來源：轉載

供稿：網友

　分組查詢

group by 關鍵字

例子

1.查詢各job_id的員工工資的最大值，最小值，平均值，總和

select job_id,max(salary),min(salary),avg(salary),sum(salary) from employees group by job_id

輸出：

JOB_ID MAX(SALARY) MIN(SALARY) AVG(SALARY) SUM(SALARY)------------------------------ ----------- ----------- ----------- -----------AC_MGR 12000 12000 12000 12000AC_ACCOUNT 8300 8300 8300 8300IT_PROG 9000 4200 5760 28800ST_MAN 8200 5800 7280 36400AD_ASST 4400 4400 4400 4400PU_MAN 11000 11000 11000 11000SH_CLERK 4200 2500 3215 64300AD_VP 17000 17000 17000 34000FI_ACCOUNT 9000 6900 7920 39600MK_MAN 13000 13000 13000 13000PR_REP 10000 10000 10000 10000

2. 選擇具有各個job_id的員工人數

select job_id,count(employee_id) from employees group by job_id

輸出：

JOB_ID COUNT(EMPLOYEE_ID)------------------------------ ------------------FI_MGR 1PU_CLERK 5SA_MAN 5MK_REP 1AD_PRES 1SA_REP 30HR_REP 1ST_CLERK 2019 rows selected.

3.查詢各個管理者手下員工的最低工資，其中最低工資不能低于6000，沒有管理者的員工不計算在內

注意： - where必須跟在 from ...之后 - having必須跟在group by...之后

select manager_id,min(salary) from employees where manager_id is not null group by manager_id having min(salary) >= 6000

輸出：

MANAGER_ID MIN(SALARY)---------- ----------- 147 6200 205 8300 108 6900 148 6100 149 6200 201 6000 102 9000 145 7000 146 70009 rows selected.

子查詢

何為子查詢？

通俗的來講，想要查詢的結果必須依賴其他查詢就是子查詢

單行子查詢

1.查詢都有誰比’Abel’的工資高

注意

子查詢要包含在括號內。將子查詢放在比較條件的右側。單行操作符對應單行子查詢，多行操作符對應多行子查詢。SQL> select last_name from employees where salary > (select salary from employees where last_name='Abel');

輸出：

LAST_NAME--------------------HartsteinHigginsKingKochharDe HaanGreenbergRussellPartnersErrazurizOzer10 rows selected.

2.查詢最低工資大于50號部門最低工資的部門id和其最低工資

該語句包含了：分組查詢、組函數、以及having關鍵字

select department_id,min(salary) from employees group by department_id having min(salary) > (select min(salary) from employees where department_id = 50);

輸出：

DEPARTMENT_ID MIN(SALARY)------------- ----------- 100 6900 30 2500 7000 20 6000 70 10000 90 17000 110 8300 40 6500 80 6100 10 4400 60 420011 rows selected.

多行子查詢

返回多行使用多行比較操作符

操作符	含義
IN	等于列表中的任意一個
ANY	和子查詢返回的某一個值比較
ALL	和子查詢返回的所有值比較

1.返回其它部門中比job_id為‘IT_PROG’部門任一工資低的員工的員工號、姓名、job_id 以及salary

select last_name,salary,job_id from employees where salary < any (select salary from employees where job_id='IT_PROG') and job_id <> 'IT_PROG';

輸出：

LAST_NAME SALARY JOB_ID------------------------------ ---------Hutton 8800 SA_REP....76 rows selected.

2.返回其它部門中比job_id為‘IT_PROG’部門所有工資都低的員工的員工號、姓名、job_id 以及salary

select last_name,salary,job_id from employees where salary < all (select salary from employees where job_id='IT_PROG') and job_id <> 'IT_PROG';

輸出：

LAST_NAME SALARY JOB_ID-------------------- --------Philtanker 2200 ST_CLERK Olson 2100 ST_CLERK....44 rows selected.

注意：上面的事76行記錄，下面的事44行記錄

3.查詢各部門中工資比本部門平均工資高的員工的員工號, 姓名和工資

select employee_id,last_name,salary from employees e1 where salary > ( select avg(salary) from employees e2 where e1.department_id = e2.department_id group by department_id )

輸出：

EMPLOYEE_ID LAST_NAME SALARY------------------------------------------193 Everett 3900.....38 rows selected.