Oracle --- Group

上一篇 / 下一篇  2010-03-29 20:58:36 / 个人分类:Oracle

1.Group by guidelines:
 1)Any column or expression in the SELECT list that is not an aggregate function must be in the GROUP BY clause.
 2) You cannot use the WHERE clause to restrict groups.You use the HAVING clause to restrict groups.
 3) Using the GROUP BY Clause on Multiple Columns
SELECT  column, group_function
FROM            table
[WHERE  condition]
[GROUP BY       group_by_expression]
[HAVING group_condition]
[ORDER BY       column];
SQL> SELECT   deptno, job, sum(sal)
  2  FROM     emp
  3  GROUP BY deptno, job;
  2  FROM      emp
  3  WHERE        job NOT LIKE 'SALES%'
  4  GROUP BY  job
  5  HAVING    SUM(sal)>5000
  6  ORDER BY  SUM(sal);
2.Guidelines for Using Subqueries:
  1)Enclose subqueries in parentheses.
  2)Place subqueries on the right side of the comparison operator.
  3)Do not add an ORDER BY clause to a subquery.
  4)Use single-row operators with single-row subqueries.
  5)Use multiple-row operators with multiple-row subqueries.
3. Types of Subqueries
  1) Single-row subqueries: Queries that return only one row from the inner SELECT statement
Operator: = >      >=   <    <=   <>
SQL> SELECT   ename, job
  2  FROM     emp
  3  WHERE    job =
  4             (SELECT          job
  5               FROM             emp
  6               WHERE            empno = 7369)
  7  AND      sal >
  8             (SELECT          sal
  9             FROM    emp
  10            WHERE   empno = 7876);
---------- ---------
The Oracle Server executes subqueries first.
The Oracle Server returns results into the main query’s HAVING clause.
SQL> SELECT     deptno, MIN(sal)
  2  FROM       emp
  3  GROUP BY   deptno
  4  HAVING     MIN(sal) >
  5             (SELECT MIN(sal)
  6             FROM    emp
  7             WHERE   deptno = 20);
  2)Multiple-row subqueries: Queries that return more than one row from the inner SELECT statement
Operator: IN,ANy,ALL
SQL> SELECT  empno, ename, job
  2  FROM    emp
  3  WHERE   sal < ANY
  4                     (SELECT sal
  5                      FROM    emp
  6                     WHERE   job = 'CLERK')
  7  AND            job <> 'CLERK';
  3)Multiple-column subqueries: Queries that return more than one column from the inner SELECT statement





Open Toolbar