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
-- 在关联子查询中，内部查询对外部查询处理的每一行执行一次

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/271063/viewspace-908663/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/271063/viewspace-908663/