SQL入門之4 group by 與子查詢

SQL入門之4 group by 與子查詢[@more@]

使用group by的注意點：
Any column or expression in the SELECT list that is
not an aggregate function must be in the GROUP BY
clause.
-- 說明：select 中的列必須在group by 子句中，在group by 中可以不在select中

?You cannot use the WHERE clause to restrict groups.
?You use the HAVING clause to restrict groups.
?You cannot use group functions in the WHERE clause.

使用子查詢的注意點：
ORA-01427: single-row subquery returns more than one row
原因是使用單行子查詢時，子查詢的返回行資料大於一行，使用in或者修改子查詢，使用之返回唯一行即可

-- Using the ANY Operator in Multiple-Row Subqueries 9000, 6000, 4200
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ANY (SELECT salary
FROM employees
WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';

-- Using the ALL Operator in Multiple-Row Subqueries 9000, 6000, 4200
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ALL (SELECT salary
FROM employees
WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';

-- SG上也有錯，盡信書不如無書
ANY means more than the minimum. =ANY is equivalent to IN.
ALL means more than the minimum.
-- 正確的說法應是：
ANY means more than the minimum. =ANY is equivalent to IN.
ALL means more than the maximum.

-- 以下有點像
-- 子查詢中有空值，其結果也返回空值
-- 子查詢中不能使用order by
-- null是個不容易對付的傢伙，問下面SQL的返回值是多少？
select case when null=null then 'ok' else 'err' end from dual;
-- WHERE 子句中的子查詢可以巢狀的層數是255
-- 在關聯子查詢中，內部查詢對外部查詢處理的每一行執行一次