jerry的成長之路

譬如平地,雖覆一簀,進,吾亡也!

2016-02-03

[SQL][概念][進階查詢]子查詢（SUBQUERY）（未完）

子類別查詢有分兩種

  1. 單列
  2. 多列
  1. 查詢 誰的 salary 大於 Abel? 
    SELECT LAST_NAME,JOB_ID,SALARY
FROM EMPLOYEES
WHERE SALARY > (SELECT SALARY FROM EMPLOYEES WHERE LAST_NAME='Abel')
  2. 查詢 誰的 salary 最低 ? 
    SELECT LAST_NAME,JOB_ID,SALARY
FROM EMPLOYEES
WHERE SALARY = (SELECT MIN(SALARY) FROM EMPLOYEES)
  3. 查詢各部門最低 salary 且 大於 部門 50 最低 salary 
    SELECT MIN(SALARY),DEPARTMENT_ID
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING MIN(SALARY) >(SELECT MIN(SALARY) FROM EMPLOYEES WHERE DEPARTMENT_ID=50 )

  4. error 單列運算符號, 子查詢傳回多列
     

    SELECT EMPLOYEE_ID,LAST_NAME 
FROM EMPLOYEES
WHERE SALARY = (SELECT MIN(SALARY) FROM EMPLOYEES GROUP BY EMPLOYEE_ID)

  5. 子查詢傳未傳回資料列
     

    SELECT LAST_NAME,JOB_ID
FROM EMPLOYEES
WHERE JOB_ID = (SELECT JOB_ID FROM EMPLOYEES WHERE LAST_NAME='HASS')

  6. ANY
     

    SELECT EMPLOYEE_ID,LAST_NAME,JOB_ID,SALARY
FROM EMPLOYEES
WHERE SALARY < ANY (SELECT SALARY FROM EMPLOYEES WHERE JOB_ID='IT_PROG')  --小於'IT_PROG'最大SALARY的人列出來
AND JOB_ID <> 'IT_PROG';    -- <>是!=的意思

    如果只有上面三行

     

    SELECT EMPLOYEE_ID,LAST_NAME,JOB_ID,SALARY
FROM EMPLOYEES
WHERE SALARY < ANY (SELECT SALARY FROM EMPLOYEES WHERE JOB_ID='IT_PROG')

  7. ALL

    SELECT EMPLOYEE_ID,LAST_NAME,JOB_ID,SALARY
FROM EMPLOYEES
WHERE SALARY < ALL (SELECT SALARY FROM EMPLOYEES WHERE JOB_ID='IT_PROG'); -- 選出小於'IT_PROG'的Minumium的值

  8. 子查詢中有空值, 找出非主管職務的員工
     

    SELECT LAST_NAME
FROM EMPLOYEES
WHERE EMPLOYEE_ID NOT IN (SELECT MANAGER_ID FROM EMPLOYEES); --因為 NOT IN 跟NULL做運算

    改寫 

  9. SELECT LAST_NAME
FROM EMPLOYEES
WHERE EMPLOYEE_ID NOT IN (SELECT MANAGER_ID FROM EMPLOYEES WHERE MANAGER_ID IS NOT NULL)

系列文章

標籤雲