SQL(高级查询)

最新推荐文章于 2024-08-15 11:17:44 发布
最新推荐文章于 2024-08-15 11:17:44 发布
阅读量511 收藏 2
点赞数
分类专栏： sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/developer_/article/details/78132666
版权


1. SQL(高级查询)

1.1. 子查询

1.1.1. 子查询在WHERE子句中

在SELECT查询中，在WHERE查询条件中的限制条件不是一个确定的值，而是来自于另外一个查询的结果。为了给查询提供数据而首先执行的查询语句叫做子查询。

子查询：嵌入在其它SQL语句中的SELECT语句，大部分时候出现在WHERE子句中。子查询嵌入的语句称作主查询或父查询。主查询可以是SELECT语句，也可以是其它类型的语句比如DML或DDL语句。

根据返回结果的不同，子查询可分为单行子查询、多行子查询及多列子查询。

图-1子查询

例如查找和SCOTT同职位的员工：

copytextpop-up

    
    
  1. SELECT e.ename, e.job
  2. FROM emp e
  3. WHERE e.job = 
  4.   (SELECT job FROM emp WHERE ename = 'SCOTT')；
SELECT e.ename, e.job
FROM emp e
WHERE e.job = 
  (SELECT job FROM emp WHERE ename = 'SCOTT')；

查找薪水比整个机构平均薪水高的员工：

copytextpop-up

    
    
  1. SELECT deptno, ename, sal
  2. FROM emp e
  3. WHERE sal> (SELECT AVG(sal) FROM emp);
SELECT deptno, ename, sal
FROM emp e
WHERE sal> (SELECT AVG(sal) FROM emp);

如果子查询返回多行，主查询中要使用多行比较操作符，包括IN、ALL、ANY。其中ALL和ANY不能单独使用，需要配合单行比较操作符>、>=、<、<=一起使用。例如查询出部门中有SALESMAN但职位不是SALESMAN的员工的信息：

copytextpop-up

    
    
  1. SELECT empno, ename, job, sal, deptno
  2. FROM emp
  3. WHERE deptno IN 
  4. (SELECT deptno FROM emp WHERE job = 'SALESMAN')
  5. AND job <> 'SALESMAN';
SELECT empno, ename, job, sal, deptno
FROM emp
WHERE deptno IN 
(SELECT deptno FROM emp WHERE job = 'SALESMAN')
AND job <> 'SALESMAN';

在子查询中需要引用到主查询的字段数据，使用EXISTS关键字。EXISTS后边的子查询至少返回一行数据，则整个条件返回TRUE。如果子查询没有结果，则返回FALSE。例如列出来那些有员工的部门信息：

copytextpop-up

    
    
  1. SELECT deptno, dname FROM dept d
  2. WHERE EXISTS 
  3.                 (SELECT * FROM emp e
  4.                  WHERE d.deptno = e.deptno);
SELECT deptno, dname FROM dept d
WHERE EXISTS 
                (SELECT * FROM emp e
                 WHERE d.deptno = e.deptno);

1.1.2. 子查询在HAVING部分

子查询不仅可以出现在WHERE子句中，还可以出现在HAVING部分。例如查询列出最低薪水高于部门30的最低薪水的部门信息：

copytextpop-up

    
    
  1. SELECT deptno, MIN(sal) min_sal
  2. FROM emp
  3. GROUP BY deptno
  4. HAVING MIN(sal) >
  5. (SELECT MIN(sal) FROM emp WHERE deptno = 30);
SELECT deptno, MIN(sal) min_sal
FROM emp
GROUP BY deptno
HAVING MIN(sal) >
(SELECT MIN(sal) FROM emp WHERE deptno = 30);

1.1.3. 子查询在FROM部分

在查询语句中，FROM子句用来指定要查询的表。如果要在一个子查询的结果中继续查询，则子查询出现在FROM 子句中，这个子查询也称作行内视图或者匿名视图。这时，把子查询当作视图对待，但视图没有名字，只能在当前的SQL语句中有效。

查询出薪水比本部门平均薪水高的员工信息：

copytextpop-up

    
    
  1. SELECT e.deptno, e.ename, e.sal
  2. FROM emp e,
  3. (SELECT deptno, AVG(sal) avg_sal FROM emp GROUP BY deptno) x
  4. WHERE e.deptno = x.deptno
  5. ande.sal>x.avg_sal
  6. ORDER BY e.deptno;
SELECT e.deptno, e.ename, e.sal
FROM emp e,
(SELECT deptno, AVG(sal) avg_sal FROM emp GROUP BY deptno) x
WHERE e.deptno = x.deptno
ande.sal>x.avg_sal
ORDER BY e.deptno;

1.1.4. 子查询在SELECT部分

把子查询放在SELECT子句部分，可以认为是外连接的另一种表现形式，使用更灵活：

copytextpop-up

    
    
  1. SELECT e.ename, e.sal, e.deptno,
  2.            (SELECT d.deptno FROM dept d 
  3.             WHERE d.deptno = e.deptno)  deptno
  4. FROM emp e;
SELECT e.ename, e.sal, e.deptno,
           (SELECT d.deptno FROM dept d 
            WHERE d.deptno = e.deptno)  deptno
FROM emp e;

1.2. 分页查询

1.2.1. ROWNUM

ROWNUM被称作伪列，用于返回标识行数据顺序的数字。例如：

copytextpop-up

    
    
  1. SELECT ROWNUM, empno, ename, sal
  2. FROM emp;
SELECT ROWNUM, empno, ename, sal
FROM emp;

ROWNUM只能从1计数，不能从结果集中直接截取。下面的查询语句将没有结果：

copytextpop-up

    
    
  1. SELECT ROWNUM, empno, ename, sal
  2. FROM emp
  3. WHERE rownum> 3;
SELECT ROWNUM, empno, ename, sal
FROM emp
WHERE rownum> 3;

如果利用ROWNUM截取结果集中的部分数据，需要用到行内视图：

copytextpop-up

    
    
  1. SELECT  * FROM 
  2.   (SELECT  ROWNUMrn , e.* FROM emp e )  
  3.  WHERE rn BETWEEN 8 AND 10;
SELECT  * FROM 
  (SELECT  ROWNUMrn , e.* FROM emp e )  
 WHERE rn BETWEEN 8 AND 10;

也就是将ROWNUM先作为行内视图的一个列，在主查询中就可以使用这个列值作为条件。

1.2.2. 使用子查询进行分页

分页策略是指每次只取一页的数据。当每次换页，取下一页的数据。在ORACLE中利用ROWNUM的功能来进行分页。

假设结果集共105条，每20条分为一页，则共6页：

Page1： 1 至 20

Page2： 21 至40

…

PageN: (n - 1) * pageSize + 1 至 n * pageSize

1.2.3. 分页与ORDER BY

按薪水倒序排列，取出结果集中第8到第10条的记录：

copytextpop-up

    
    
  1. SELECT  * FROM 
  2.   (SELECT  ROWNUMrn , t.* FROM 
  3.     (SELECT empno,ename,sal FROM emp
  4.      ORDER BY sal DESC)  t
  5.   )  
  6.  WHERE rn BETWEEN 8 AND 10;
SELECT  * FROM 
  (SELECT  ROWNUMrn , t.* FROM 
	(SELECT empno,ename,sal FROM emp
     ORDER BY sal DESC)  t
  )  
 WHERE rn BETWEEN 8 AND 10;

根据要查看的页数，计算起点值（(n - 1) * pageSize + 1）和终点值（n * pageSize），替换掉BETWEEN和AND的参数，即得到当前页的记录。

1.3. DECODE函数

1.3.1. DECODE函数基本语法

DECODE函数的语法如下：

DECODE (expr, search1, result1[, search2, result2…][, default])

它用于比较参数expr的值，如果匹配到哪一个search条件，就返回对应的result结果，可以有多组search和result的对应关系，如果任何一个search条件都没有匹配到，则返回最后default的值。default参数是可选的，如果没有提供default参数值，当没有匹配到时，将返回NULL。

查询职员表，根据职员的职位计算奖励金额，当职位分别是’MANAGER’、’ANALYST’、’SALESMAN’时，奖励金额分别是薪水的1.2倍、1.1倍、1.05倍，如果不是这三个职位，则奖励金额取薪水值：

copytextpop-up

    
    
  1. SELECT ename, job, sal,
  2. DECODE(job, 'MANAGER', sal * 1.2,
  3.                'ANALYST', sal * 1.1,
  4.               'SALESMAN', sal * 1.05,
  5. sal
  6.      ) bonus
  7. FROM emp;
SELECT ename, job, sal,
DECODE(job, 'MANAGER', sal * 1.2,
               'ANALYST', sal * 1.1,
              'SALESMAN', sal * 1.05,
sal
     ) bonus
FROM emp;

和DECODE函数功能相似的有CASE语句，实现类似于if-else的操作。

copytextpop-up

    
    
  1. SELECT ename, job, sal,
  2.      CASE job WHEN 'MANAGER' THEN sal * 1.2
  3.       WHEN 'ANALYST' THEN sal * 1.1
  4.      WHEN 'SALESMAN' THEN sal * 1.05
  5.                     ELSE sal END
  6. bonus
  7. FROM emp;
SELECT ename, job, sal,
     CASE job WHEN 'MANAGER' THEN sal * 1.2
      WHEN 'ANALYST' THEN sal * 1.1
     WHEN 'SALESMAN' THEN sal * 1.05
                    ELSE sal END
bonus
FROM emp;

1.3.2. DECODE函数在分组查询中的应用

DECODE函数可以按字段内容分组，例如：计算职位的人数，analyst/manager职位属于vip，其余是普通员工operation,这种功能无法用GROUP BY简单实现。用decode的实现方式：

copytextpop-up

    
    
  1. SELECT DECODE(job, 'ANALYST', 'VIP', 
  2.                        'MANAGER', 'VIP', 
  3.                        'OPERATION') job,
  4. COUNT(1) job_cnt
  5. FROM emp
  6. GROUP BY DECODE(job, 'ANALYST', 'VIP', 'MANAGER', 'VIP', 'OPERATION');
SELECT DECODE(job, 'ANALYST', 'VIP', 
                       'MANAGER', 'VIP', 
                       'OPERATION') job,
COUNT(1) job_cnt
FROM emp
GROUP BY DECODE(job, 'ANALYST', 'VIP', 'MANAGER', 'VIP', 'OPERATION');

图-2DECODE函数的运行结果

DECODE函数也可以按字段内容排序，例如：Dept表中按”研发部”、“市场部”、“销售部”排序，用普通的select语句，无法按照字面数据排序：

copytextpop-up

    
    
  1. SELECT deptno, dname, loc
  2. FROM dept
  3. ORDER BY 
  4.       DECODE(dname, '研发部',1,'市场部',2,'销售部',3), loc;
SELECT deptno, dname, loc
FROM dept
ORDER BY 
      DECODE(dname, '研发部',1,'市场部',2,'销售部',3), loc;

1.4. 排序函数

1.4.1. ROW_NUMBER

ROW_NUMBER语法如下：

copytextpop-up

    
    
  1. ROW_NUMBER() OVER(
  2.           PARTITION BY col1 ORDER BY col2)
ROW_NUMBER() OVER(
          PARTITION BY col1 ORDER BY col2)

表示根据col1分组，在分组内部根据col2排序。此函数计算的值就表示每组内部排序后的顺序编号，组内连续且唯一。

ROWNUM是伪列， ROW_NUMBER功能更强，可以直接从结果集中取出子集。

场景：按照部门编码分组显示，每组内按职员编码排序，并赋予组内编码

copytextpop-up

    
    
  1. SELECT deptno, ename, empno,
  2.  ROW_NUMBER()
  3.    OVER (PARTITION BY deptno ORDER BY empno)     AS emp_id
  4.    FROM emp;
  5. 

SELECT deptno, ename, empno,
 ROW_NUMBER()
   OVER (PARTITION BY deptno ORDER BY empno)     AS emp_id
   FROM emp;

1.4.2. RANK

RANK函数的语法如下：

copytextpop-up

    
    
  1. RANK() OVER(
  2.           PARTITION BY col1 ORDER BY col2)
RANK() OVER(
          PARTITION BY col1 ORDER BY col2)

表示根据col1分组，在分组内部根据col2给予等级标识，即排名，相同的数据返回相同排名。特点是跳跃排序，如果有相同数据，则排名相同，比如并列第二，则两行数据都标记为2，但下一位将是第四名。

和ROW_NUMBER的区别是有结果有重复值，而ROW_NUMBER没有。

场景：按照部门编码分组，同组内按薪水倒序排序，相同薪水则按奖金数正序排序，并给予组内等级，用Rank_ID表示

copytextpop-up

    
    
  1. SELECT deptno, ename, sal, comm,
  2. RANK() OVER (PARTITION BY deptno
  3.    ORDER BY sal DESC, comm) "Rank_ID"
  4.    FROM emp；
SELECT deptno, ename, sal, comm,
RANK() OVER (PARTITION BY deptno
   ORDER BY sal DESC, comm) "Rank_ID"
   FROM emp；

1.4.3. DENSE_RANK

DENSE_RANK函数的语法如下：

copytextpop-up

    
    
  1. DENSE_RANK() OVER(
  2.           PARTITION BY col1 ORDER BY col2)
DENSE_RANK() OVER(
          PARTITION BY col1 ORDER BY col2)

表示根据col1分组，在分组内部根据col2给予等级标识，即排名，相同的数据返回相同排名。特点是连续排序，如果有并列第二，下一个排序将是三，这一点是和RANK的不同，RANK是跳跃排序。

场景：关联emp和dept表，按照部门编码分组，每组内按照员工薪水排序，列出员工的部门名字、姓名和薪水：

copytextpop-up

    
    
  1. SELECT d.dname, e.ename, e.sal, 
  2.             DENSE_RANK()
  3.       OVER (PARTITION BY e.deptno ORDER BY e.sal)     
  4.             AS drank
  5.    FROM emp e join dept d
  6. one.deptno = d.deptno;
SELECT d.dname, e.ename, e.sal, 
            DENSE_RANK()
      OVER (PARTITION BY e.deptno ORDER BY e.sal)     
            AS drank
   FROM emp e join dept d
one.deptno = d.deptno;

1.5. 高级分组函数

1.5.1. ROLLUP

ROLLUP、CUBE 和 GROUPING SETS 运算符是 GROUP BY 子句的扩展，可以生成与使用 UNION ALL 来组合单个分组查询时相同的结果集，用来简化和高效的实现统计查询。语法形式如下：

  • GROUP BY ROLLUP(a, b, c)
  • GROUP BY CUBE(a, b, c)
  • GROUP BY GROUPING SETS ( (a), (b))

假设有表test，有a、b、c、d四个列。

copytextpop-up

    
    
  1. SELECT a,b,c,SUM(d) FROM test GROUP BY ROLLUP(a,b,c);
SELECT a,b,c,SUM(d) FROM test GROUP BY ROLLUP(a,b,c);

等价于：

copytextpop-up

    
    
  1. SELECT a,b,c,SUM(d) FROM test GROUP BY a,b,c
  2. UNION ALL
  3. SELECT a,b,null,SUM(d) FROM test GROUP BY a,b
  4. UNION ALL
  5. SELECT a,null,null,SUM(d) FROM test GROUP BY a
  6. UNION ALL
  7. SELECT null,null,null,sum(d) FROM test;
SELECT a,b,c,SUM(d) FROM test GROUP BY a,b,c
UNION ALL
SELECT a,b,null,SUM(d) FROM test GROUP BY a,b
UNION ALL
SELECT a,null,null,SUM(d) FROM test GROUP BY a
UNION ALL
SELECT null,null,null,sum(d) FROM test;

对ROLLUP的列从右到左以一次少一列的方式进行分组直到所有列都去掉后的分组(也就是全表分组)。对于n个参数的ROLLUP，有n+1次分组。

表－1 数据样例表

准备数据：

copytextpop-up

    
    
  1. SQL>DROP TABLE sales_tab;
  2. SQL>CREATE TABLE sales_tab (
  3. year_id   NUMBER NOT NULL,
  4. month_id   NUMBER NOT NULL,
  5. day_id   NUMBER NOT NULL,
  6. sales_value NUMBER(10,2) NOT NULL
  7. );
  8. SQL>INSERT INTO sales_tab
  9. SELECT TRUNC(DBMS_RANDOM.value(low => 2010, high => 2012)) AS year_id,
  10. TRUNC(DBMS_RANDOM.value(low => 1, high => 13)) AS month_id,
  11. TRUNC(DBMS_RANDOM.value(low => 1, high => 32)) AS day_id,
  12. ROUND(DBMS_RANDOM.value(low => 1, high => 100), 2) AS sales_value
  13. FROM   dual
  14. CONNECT BY level <= 1000;
  15. SQL>COMMIT;
SQL>DROP TABLE sales_tab;
SQL>CREATE TABLE sales_tab (
year_id   NUMBER NOT NULL,
month_id   NUMBER NOT NULL,
day_id   NUMBER NOT NULL,
sales_value NUMBER(10,2) NOT NULL
);
SQL>INSERT INTO sales_tab
SELECT TRUNC(DBMS_RANDOM.value(low => 2010, high => 2012)) AS year_id,
TRUNC(DBMS_RANDOM.value(low => 1, high => 13)) AS month_id,
TRUNC(DBMS_RANDOM.value(low => 1, high => 32)) AS day_id,
ROUND(DBMS_RANDOM.value(low => 1, high => 100), 2) AS sales_value
FROM   dual
CONNECT BY level <= 1000;
SQL>COMMIT;

复习组函数的用法：

copytextpop-up

    
    
  1. SQL>SELECT SUM(sales_value) AS sales_value FROM   sales_tab;
  2. SQL>SELECT year_id, COUNT(*) AS num_rows,
  3. SUM(sales_value) AS sales_value
  4. FROM   sales_tab
  5. GROUP BY year_id
  6. ORDER BY year_id;
  7. SQL>SELECT year_id, month_id,
  8. COUNT(*) AS num_rows,
  9. SUM(sales_value) AS sales_value
  10. FROM   sales_tab
  11. GROUP BY year_id, month_id
  12. ORDER BY year_id, month_id;
SQL>SELECT SUM(sales_value) AS sales_value FROM   sales_tab;
SQL>SELECT year_id, COUNT(*) AS num_rows,
SUM(sales_value) AS sales_value
FROM   sales_tab
GROUP BY year_id
ORDER BY year_id;
SQL>SELECT year_id, month_id,
COUNT(*) AS num_rows,
SUM(sales_value) AS sales_value
FROM   sales_tab
GROUP BY year_id, month_id
ORDER BY year_id, month_id;

图-3 在测试表中使用组函数的运行结果

图-4 在测试表中使用组函数的运行结果

ROLLUP函数的用法：

copytextpop-up

    
    
  1. SELECT year_id, month_id, 
  2. SUM(sales_value) AS sales_value
  3. FROM   sales_tab
  4. GROUP BY 
  5. ROLLUP (year_id, month_id)
  6. ORDER BY year_id, month_id;
SELECT year_id, month_id, 
SUM(sales_value) AS sales_value
FROM   sales_tab
GROUP BY 
ROLLUP (year_id, month_id)
ORDER BY year_id, month_id;

图-5 在测试表中使用ROLLUP函数的运行结果

copytextpop-up

    
    
  1. SELECT year_id, month_id, day_id, SUM(sales_value) AS sales_value
  2. FROM   sales_tab
  3. GROUP BY ROLLUP (year_id, month_id, day_id)
  4. ORDER BY year_id, month_id, day_id;
SELECT year_id, month_id, day_id, SUM(sales_value) AS sales_value
FROM   sales_tab
GROUP BY ROLLUP (year_id, month_id, day_id)
ORDER BY year_id, month_id, day_id;

图-6 在测试表中使用ROLLUP函数的运行结果

1.5.2. CUBE

CUBE函数的语法形式：

copytextpop-up

    
    
  1. GROUP BY CUBE(a, b, c)
GROUP BY CUBE(a, b, c)

对cube的每个参数，都可以理解为取值为参与分组和不参与分组两个值的一个维度，所有维度取值组合的集合就是分组后的集合。对于n个参数的cube，有2^n次分组。

如果GROUP BY CUBE(a,b,c)，首先对(a,b,c)进行GROUP BY，然后依次是(a,b)，(a,c)，(a)，(b,c)，(b)，(c)，最后对全表进行GROUP BY操作，所以一共是2^3=8次分组。

copytextpop-up

    
    
  1. SELECT a,b,c,SUM(d) FROM test GROUP BY CUBE(a,b,c);
SELECT a,b,c,SUM(d) FROM test GROUP BY CUBE(a,b,c);

等价于：

copytextpop-up

    
    
  1. SELECT a,b,c,SUM(d) FROM test GROUP BY a,b,c
  2. UNION ALL
  3. SELECT a,b,NULL,SUM(d) FROM test GROUP BY a,b
  4. UNION ALL
  5. SELECT a,NULL,c,SUM(d) FROM test GROUP BY a,c
  6. UNION ALL
  7. SELECT a,NULL,NULL,SUM(d) FROM test GROUP BY a
  8. UNION ALL
  9. SELECT NULL,b,c,SUM(d) FROM test GROUP BY b,c
  10. UNION ALL
  11. SELECT NULL,b,NULL,SUM(d) FROM test GROUP BY b
  12. UNION ALL
  13. SELECT NULL,NULL,c,SUM(d) FROM test GROUP BY c
  14. UNION ALL
  15. SELECT NULL,NULL,NULL,SUM(d) FROM test ;
SELECT a,b,c,SUM(d) FROM test GROUP BY a,b,c
UNION ALL
SELECT a,b,NULL,SUM(d) FROM test GROUP BY a,b
UNION ALL
SELECT a,NULL,c,SUM(d) FROM test GROUP BY a,c
UNION ALL
SELECT a,NULL,NULL,SUM(d) FROM test GROUP BY a
UNION ALL
SELECT NULL,b,c,SUM(d) FROM test GROUP BY b,c
UNION ALL
SELECT NULL,b,NULL,SUM(d) FROM test GROUP BY b
UNION ALL
SELECT NULL,NULL,c,SUM(d) FROM test GROUP BY c
UNION ALL
SELECT NULL,NULL,NULL,SUM(d) FROM test ;

等价于只是方便理解，其内部运行机制并不相同，其效率远高于UNION ALL。

在sales_value表中使用cube函数：

copytextpop-up

    
    
  1. SELECT year_id, month_id,
  2. SUM(sales_value) AS sales_value
  3. FROM   sales_tab
  4. GROUP BY CUBE (year_id, month_id)
  5. ORDER BY year_id, month_id;
SELECT year_id, month_id,
SUM(sales_value) AS sales_value
FROM   sales_tab
GROUP BY CUBE (year_id, month_id)
ORDER BY year_id, month_id;

图-7在测试表中使用CUBE函数的运行结果

copytextpop-up

    
    
  1. SELECT year_id,  month_id, day_id,
  2. SUM(sales_value) AS sales_value
  3. FROM   sales_tab
  4. GROUP BY CUBE (year_id, month_id, day_id)
  5. ORDER BY year_id, month_id, day_id;
SELECT year_id,  month_id, day_id,
SUM(sales_value) AS sales_value
FROM   sales_tab
GROUP BY CUBE (year_id, month_id, day_id)
ORDER BY year_id, month_id, day_id;

1.5.3. GROUPING SETS

GROUPING SETS 运算符可以生成与使用单个 GROUP BY、ROLLUP 或 CUBE 运算符所生成的结果集相同的结果集，但是使用更灵活。

如果不需要获得由完备的 ROLLUP 或 CUBE 运算符生成的全部分组，则可以使用 GROUPING SETS 仅指定所需的分组。GROUPING SETS 列表可以包含重复的分组。

GROUPING SETS示例：

copytextpop-up

    
    
  1. SELECT year_id, month_id, SUM(sales_value)  
  2. FROM sales_tab
  3. GROUP BY CUBE (year_id,month_id)
  4. order by 1, 2; 
  5.  
  6. SELECT year_id, month_id, SUM(sales_value)  
  7. FROM sales_tab
  8. GROUP BY GROUPING SETS (  (year_id), (month_id))
  9. order by 1, 2
SELECT year_id, month_id, SUM(sales_value)  
FROM sales_tab
GROUP BY CUBE (year_id,month_id)
order by 1, 2; 

SELECT year_id, month_id, SUM(sales_value)  
FROM sales_tab
GROUP BY GROUPING SETS (  (year_id), (month_id))
order by 1, 2

其中分组方式示例如下：

  • 使用GROUP BY GROUPING SETS(a,b,c)，则对(a),(b),(c)进行GROUP BY
  • 使用GROUP BY GROUPING SETS((a,b),c), 则对(a,b),(c)进行GROUP BY
  • GROUPING BY GROUPING SET(a,a) , 则对(a)进行2次GROUP BY, GROUPING SETS的参数允许重复 

1.6. 集合操作

1.6.1. UNION、UNION ALL

图-8 集合操作

为了合并多个SELECT语句的结果，可以使用集合操作符，实现集合的并、交、差。

集合操作符包括UNION、UNION ALL、INTERSECT和MINUS。多条作集合操作的SELECT语句的列的个数和数据类型必须匹配。

ORDER BY子句只能放在最后的一个查询语句中。

集合操作的语法如下：

copytextpop-up

    
    
  1. SELECT statement1
  2. [UNION | UNION ALL | INTERSECT | MINUS]
  3. SELECT statement2;
SELECT statement1
[UNION | UNION ALL | INTERSECT | MINUS]
SELECT statement2;

UNION和UNION ALL用来获取两个或两个以上结果集的并集：

  • UNION操作符会自动去掉合并后的重复记录。
  • UNION ALL返回两个结果集中的所有行，包括重复的行。
  • UNION操作符对查询结果排序，UNION ALL不排序。

例如，合并职位是’MANAGER’的员工和薪水大于2500的员工集合，查看两种方式的结果差别：

copytextpop-up

    
    
  1. --Union
  2. SELECT ename, job, sal FROM emp
  3. WHERE job = 'MANAGER'
  4. SELECT ename, job, sal FROM emp
  5. WHERE sal> 2500;
  6.  
  7. --Union all
  8. SELECT ename, job, sal FROM emp
  9. WHERE job = 'MANAGER'
  10. SELECT ename, job, sal FROM emp
  11. WHERE sal> 2500;
--Union
SELECT ename, job, sal FROM emp
WHERE job = 'MANAGER'
SELECT ename, job, sal FROM emp
WHERE sal> 2500;

--Union all
SELECT ename, job, sal FROM emp
WHERE job = 'MANAGER'
SELECT ename, job, sal FROM emp
WHERE sal> 2500;

1.6.2. INTERSECT

INTERSECT函数获得两个结果集的交集，只有同时存在于两个结果集中的数据，才被显示输出。使用INTERSECT操作符后的结果集会以第一列的数据作升序排列。

例如：显示职位是’MANAGER’的员工和薪水大于2500的员工的交集：

copytextpop-up

    
    
  1. SELECT ename, job, sal FROM emp
  2. WHERE job = 'MANAGER'
  3. INTERSECT
  4. SELECT ename, job, sal FROM emp
  5. WHERE sal> 2500;
SELECT ename, job, sal FROM emp
WHERE job = 'MANAGER'
INTERSECT
SELECT ename, job, sal FROM emp
WHERE sal> 2500;

1.6.3. MINUS

MINUS函数获取两个结果集的差集。只有在第一个结果集中存在，在第二个结果集中不存在的数据，才能够被显示出来。也就是结果集一减去结果集二的结果。

例如：列出职位是MANAGER但薪水低于2500的员工记录：

copytextpop-up

    
    
  1. SELECT ename, job, sal FROM emp
  2. WHERE job = 'MANAGER'
  3. MINUS
  4. SELECT ename, job, sal FROM emp
  5. WHERE sal> 2500;
SELECT ename, job, sal FROM emp
WHERE job = 'MANAGER'
MINUS
SELECT ename, job, sal FROM emp
WHERE sal> 2500;

1. SQL(高级查询)

1.1. 子查询

1.1.1. 子查询在WHERE子句中

在SELECT查询中，在WHERE查询条件中的限制条件不是一个确定的值，而是来自于另外一个查询的结果。为了给查询提供数据而首先执行的查询语句叫做子查询。

子查询：嵌入在其它SQL语句中的SELECT语句，大部分时候出现在WHERE子句中。子查询嵌入的语句称作主查询或父查询。主查询可以是SELECT语句，也可以是其它类型的语句比如DML或DDL语句。

根据返回结果的不同，子查询可分为单行子查询、多行子查询及多列子查询。

图-1子查询

例如查找和SCOTT同职位的员工：

copytextpop-up

     
     
  1. SELECT e.ename, e.job
  2. FROM emp e
  3. WHERE e.job = 
  4.   (SELECT job FROM emp WHERE ename = 'SCOTT')；
SELECT e.ename, e.job
FROM emp e
WHERE e.job = 
  (SELECT job FROM emp WHERE ename = 'SCOTT')；

查找薪水比整个机构平均薪水高的员工：

copytextpop-up

     
     
  1. SELECT deptno, ename, sal
  2. FROM emp e
  3. WHERE sal> (SELECT AVG(sal) FROM emp);
SELECT deptno, ename, sal
FROM emp e
WHERE sal> (SELECT AVG(sal) FROM emp);

如果子查询返回多行，主查询中要使用多行比较操作符，包括IN、ALL、ANY。其中ALL和ANY不能单独使用，需要配合单行比较操作符>、>=、<、<=一起使用。例如查询出部门中有SALESMAN但职位不是SALESMAN的员工的信息：

copytextpop-up

     
     
  1. SELECT empno, ename, job, sal, deptno
  2. FROM emp
  3. WHERE deptno IN 
  4. (SELECT deptno FROM emp WHERE job = 'SALESMAN')
  5. AND job <> 'SALESMAN';
SELECT empno, ename, job, sal, deptno
FROM emp
WHERE deptno IN 
(SELECT deptno FROM emp WHERE job = 'SALESMAN')
AND job <> 'SALESMAN';

在子查询中需要引用到主查询的字段数据，使用EXISTS关键字。EXISTS后边的子查询至少返回一行数据，则整个条件返回TRUE。如果子查询没有结果，则返回FALSE。例如列出来那些有员工的部门信息：

copytextpop-up

     
     
  1. SELECT deptno, dname FROM dept d
  2. WHERE EXISTS 
  3.                 (SELECT * FROM emp e
  4.                  WHERE d.deptno = e.deptno);
SELECT deptno, dname FROM dept d
WHERE EXISTS 
                (SELECT * FROM emp e
                 WHERE d.deptno = e.deptno);

1.1.2. 子查询在HAVING部分

子查询不仅可以出现在WHERE子句中，还可以出现在HAVING部分。例如查询列出最低薪水高于部门30的最低薪水的部门信息：

copytextpop-up

     
     
  1. SELECT deptno, MIN(sal) min_sal
  2. FROM emp
  3. GROUP BY deptno
  4. HAVING MIN(sal) >
  5. (SELECT MIN(sal) FROM emp WHERE deptno = 30);
SELECT deptno, MIN(sal) min_sal
FROM emp
GROUP BY deptno
HAVING MIN(sal) >
(SELECT MIN(sal) FROM emp WHERE deptno = 30);

1.1.3. 子查询在FROM部分

在查询语句中，FROM子句用来指定要查询的表。如果要在一个子查询的结果中继续查询，则子查询出现在FROM 子句中，这个子查询也称作行内视图或者匿名视图。这时，把子查询当作视图对待，但视图没有名字，只能在当前的SQL语句中有效。

查询出薪水比本部门平均薪水高的员工信息：

copytextpop-up

     
     
  1. SELECT e.deptno, e.ename, e.sal
  2. FROM emp e,
  3. (SELECT deptno, AVG(sal) avg_sal FROM emp GROUP BY deptno) x
  4. WHERE e.deptno = x.deptno
  5. ande.sal>x.avg_sal
  6. ORDER BY e.deptno;
SELECT e.deptno, e.ename, e.sal
FROM emp e,
(SELECT deptno, AVG(sal) avg_sal FROM emp GROUP BY deptno) x
WHERE e.deptno = x.deptno
ande.sal>x.avg_sal
ORDER BY e.deptno;

1.1.4. 子查询在SELECT部分

把子查询放在SELECT子句部分，可以认为是外连接的另一种表现形式，使用更灵活：

copytextpop-up

     
     
  1. SELECT e.ename, e.sal, e.deptno,
  2.            (SELECT d.deptno FROM dept d 
  3.             WHERE d.deptno = e.deptno)  deptno
  4. FROM emp e;
SELECT e.ename, e.sal, e.deptno,
           (SELECT d.deptno FROM dept d 
            WHERE d.deptno = e.deptno)  deptno
FROM emp e;

1.2. 分页查询

1.2.1. ROWNUM

ROWNUM被称作伪列，用于返回标识行数据顺序的数字。例如：

copytextpop-up

     
     
  1. SELECT ROWNUM, empno, ename, sal
  2. FROM emp;
SELECT ROWNUM, empno, ename, sal
FROM emp;

ROWNUM只能从1计数，不能从结果集中直接截取。下面的查询语句将没有结果：

copytextpop-up

     
     
  1. SELECT ROWNUM, empno, ename, sal
  2. FROM emp
  3. WHERE rownum> 3;
SELECT ROWNUM, empno, ename, sal
FROM emp
WHERE rownum> 3;

如果利用ROWNUM截取结果集中的部分数据，需要用到行内视图：

copytextpop-up

     
     
  1. SELECT  * FROM 
  2.   (SELECT  ROWNUMrn , e.* FROM emp e )  
  3.  WHERE rn BETWEEN 8 AND 10;
SELECT  * FROM 
  (SELECT  ROWNUMrn , e.* FROM emp e )  
 WHERE rn BETWEEN 8 AND 10;

也就是将ROWNUM先作为行内视图的一个列，在主查询中就可以使用这个列值作为条件。

1.2.2. 使用子查询进行分页

分页策略是指每次只取一页的数据。当每次换页，取下一页的数据。在ORACLE中利用ROWNUM的功能来进行分页。

假设结果集共105条，每20条分为一页，则共6页：

Page1： 1 至 20

Page2： 21 至40

…

PageN: (n - 1) * pageSize + 1 至 n * pageSize

1.2.3. 分页与ORDER BY

按薪水倒序排列，取出结果集中第8到第10条的记录：

copytextpop-up

     
     
  1. SELECT  * FROM 
  2.   (SELECT  ROWNUMrn , t.* FROM 
  3.     (SELECT empno,ename,sal FROM emp
  4.      ORDER BY sal DESC)  t
  5.   )  
  6.  WHERE rn BETWEEN 8 AND 10;
SELECT  * FROM 
  (SELECT  ROWNUMrn , t.* FROM 
	(SELECT empno,ename,sal FROM emp
     ORDER BY sal DESC)  t
  )  
 WHERE rn BETWEEN 8 AND 10;

根据要查看的页数，计算起点值（(n - 1) * pageSize + 1）和终点值（n * pageSize），替换掉BETWEEN和AND的参数，即得到当前页的记录。

1.3. DECODE函数

1.3.1. DECODE函数基本语法

DECODE函数的语法如下：

DECODE (expr, search1, result1[, search2, result2…][, default])

它用于比较参数expr的值，如果匹配到哪一个search条件，就返回对应的result结果，可以有多组search和result的对应关系，如果任何一个search条件都没有匹配到，则返回最后default的值。default参数是可选的，如果没有提供default参数值，当没有匹配到时，将返回NULL。

查询职员表，根据职员的职位计算奖励金额，当职位分别是’MANAGER’、’ANALYST’、’SALESMAN’时，奖励金额分别是薪水的1.2倍、1.1倍、1.05倍，如果不是这三个职位，则奖励金额取薪水值：

copytextpop-up

     
     
  1. SELECT ename, job, sal,
  2. DECODE(job, 'MANAGER', sal * 1.2,
  3.                'ANALYST', sal * 1.1,
  4.               'SALESMAN', sal * 1.05,
  5. sal
  6.      ) bonus
  7. FROM emp;
SELECT ename, job, sal,
DECODE(job, 'MANAGER', sal * 1.2,
               'ANALYST', sal * 1.1,
              'SALESMAN', sal * 1.05,
sal
     ) bonus
FROM emp;

和DECODE函数功能相似的有CASE语句，实现类似于if-else的操作。

copytextpop-up

     
     
  1. SELECT ename, job, sal,
  2.      CASE job WHEN 'MANAGER' THEN sal * 1.2
  3.       WHEN 'ANALYST' THEN sal * 1.1
  4.      WHEN 'SALESMAN' THEN sal * 1.05
  5.                     ELSE sal END
  6. bonus
  7. FROM emp;
SELECT ename, job, sal,
     CASE job WHEN 'MANAGER' THEN sal * 1.2
      WHEN 'ANALYST' THEN sal * 1.1
     WHEN 'SALESMAN' THEN sal * 1.05
                    ELSE sal END
bonus
FROM emp;

1.3.2. DECODE函数在分组查询中的应用

DECODE函数可以按字段内容分组，例如：计算职位的人数，analyst/manager职位属于vip，其余是普通员工operation,这种功能无法用GROUP BY简单实现。用decode的实现方式：

copytextpop-up

     
     
  1. SELECT DECODE(job, 'ANALYST', 'VIP', 
  2.                        'MANAGER', 'VIP', 
  3.                        'OPERATION') job,
  4. COUNT(1) job_cnt
  5. FROM emp
  6. GROUP BY DECODE(job, 'ANALYST', 'VIP', 'MANAGER', 'VIP', 'OPERATION');
SELECT DECODE(job, 'ANALYST', 'VIP', 
                       'MANAGER', 'VIP', 
                       'OPERATION') job,
COUNT(1) job_cnt
FROM emp
GROUP BY DECODE(job, 'ANALYST', 'VIP', 'MANAGER', 'VIP', 'OPERATION');

图-2DECODE函数的运行结果

DECODE函数也可以按字段内容排序，例如：Dept表中按”研发部”、“市场部”、“销售部”排序，用普通的select语句，无法按照字面数据排序：

copytextpop-up

     
     
  1. SELECT deptno, dname, loc
  2. FROM dept
  3. ORDER BY 
  4.       DECODE(dname, '研发部',1,'市场部',2,'销售部',3), loc;
SELECT deptno, dname, loc
FROM dept
ORDER BY 
      DECODE(dname, '研发部',1,'市场部',2,'销售部',3), loc;

1.4. 排序函数

1.4.1. ROW_NUMBER

ROW_NUMBER语法如下：

copytextpop-up

     
     
  1. ROW_NUMBER() OVER(
  2.           PARTITION BY col1 ORDER BY col2)
ROW_NUMBER() OVER(
          PARTITION BY col1 ORDER BY col2)

表示根据col1分组，在分组内部根据col2排序。此函数计算的值就表示每组内部排序后的顺序编号，组内连续且唯一。

ROWNUM是伪列， ROW_NUMBER功能更强，可以直接从结果集中取出子集。

场景：按照部门编码分组显示，每组内按职员编码排序，并赋予组内编码

copytextpop-up

     
     
  1. SELECT deptno, ename, empno,
  2.  ROW_NUMBER()
  3.    OVER (PARTITION BY deptno ORDER BY empno)     AS emp_id
  4.    FROM emp;
  5. 

SELECT deptno, ename, empno,
 ROW_NUMBER()
   OVER (PARTITION BY deptno ORDER BY empno)     AS emp_id
   FROM emp;

1.4.2. RANK

RANK函数的语法如下：

copytextpop-up

     
     
  1. RANK() OVER(
  2.           PARTITION BY col1 ORDER BY col2)
RANK() OVER(
          PARTITION BY col1 ORDER BY col2)

表示根据col1分组，在分组内部根据col2给予等级标识，即排名，相同的数据返回相同排名。特点是跳跃排序，如果有相同数据，则排名相同，比如并列第二，则两行数据都标记为2，但下一位将是第四名。

和ROW_NUMBER的区别是有结果有重复值，而ROW_NUMBER没有。

场景：按照部门编码分组，同组内按薪水倒序排序，相同薪水则按奖金数正序排序，并给予组内等级，用Rank_ID表示

copytextpop-up

     
     
  1. SELECT deptno, ename, sal, comm,
  2. RANK() OVER (PARTITION BY deptno
  3.    ORDER BY sal DESC, comm) "Rank_ID"
  4.    FROM emp；
SELECT deptno, ename, sal, comm,
RANK() OVER (PARTITION BY deptno
   ORDER BY sal DESC, comm) "Rank_ID"
   FROM emp；

1.4.3. DENSE_RANK

DENSE_RANK函数的语法如下：

copytextpop-up

     
     
  1. DENSE_RANK() OVER(
  2.           PARTITION BY col1 ORDER BY col2)
DENSE_RANK() OVER(
          PARTITION BY col1 ORDER BY col2)

表示根据col1分组，在分组内部根据col2给予等级标识，即排名，相同的数据返回相同排名。特点是连续排序，如果有并列第二，下一个排序将是三，这一点是和RANK的不同，RANK是跳跃排序。

场景：关联emp和dept表，按照部门编码分组，每组内按照员工薪水排序，列出员工的部门名字、姓名和薪水：

copytextpop-up

     
     
  1. SELECT d.dname, e.ename, e.sal, 
  2.             DENSE_RANK()
  3.       OVER (PARTITION BY e.deptno ORDER BY e.sal)     
  4.             AS drank
  5.    FROM emp e join dept d
  6. one.deptno = d.deptno;
SELECT d.dname, e.ename, e.sal, 
            DENSE_RANK()
      OVER (PARTITION BY e.deptno ORDER BY e.sal)     
            AS drank
   FROM emp e join dept d
one.deptno = d.deptno;

1.5. 高级分组函数

1.5.1. ROLLUP

ROLLUP、CUBE 和 GROUPING SETS 运算符是 GROUP BY 子句的扩展，可以生成与使用 UNION ALL 来组合单个分组查询时相同的结果集，用来简化和高效的实现统计查询。语法形式如下：

  • GROUP BY ROLLUP(a, b, c)
  • GROUP BY CUBE(a, b, c)
  • GROUP BY GROUPING SETS ( (a), (b))

假设有表test，有a、b、c、d四个列。

copytextpop-up

     
     
  1. SELECT a,b,c,SUM(d) FROM test GROUP BY ROLLUP(a,b,c);
SELECT a,b,c,SUM(d) FROM test GROUP BY ROLLUP(a,b,c);

等价于：

copytextpop-up

     
     
  1. SELECT a,b,c,SUM(d) FROM test GROUP BY a,b,c
  2. UNION ALL
  3. SELECT a,b,null,SUM(d) FROM test GROUP BY a,b
  4. UNION ALL
  5. SELECT a,null,null,SUM(d) FROM test GROUP BY a
  6. UNION ALL
  7. SELECT null,null,null,sum(d) FROM test;
SELECT a,b,c,SUM(d) FROM test GROUP BY a,b,c
UNION ALL
SELECT a,b,null,SUM(d) FROM test GROUP BY a,b
UNION ALL
SELECT a,null,null,SUM(d) FROM test GROUP BY a
UNION ALL
SELECT null,null,null,sum(d) FROM test;

对ROLLUP的列从右到左以一次少一列的方式进行分组直到所有列都去掉后的分组(也就是全表分组)。对于n个参数的ROLLUP，有n+1次分组。

表－1 数据样例表

准备数据：

copytextpop-up

     
     
  1. SQL>DROP TABLE sales_tab;
  2. SQL>CREATE TABLE sales_tab (
  3. year_id   NUMBER NOT NULL,
  4. month_id   NUMBER NOT NULL,
  5. day_id   NUMBER NOT NULL,
  6. sales_value NUMBER(10,2) NOT NULL
  7. );
  8. SQL>INSERT INTO sales_tab
  9. SELECT TRUNC(DBMS_RANDOM.value(low => 2010, high => 2012)) AS year_id,
  10. TRUNC(DBMS_RANDOM.value(low => 1, high => 13)) AS month_id,
  11. TRUNC(DBMS_RANDOM.value(low => 1, high => 32)) AS day_id,
  12. ROUND(DBMS_RANDOM.value(low => 1, high => 100), 2) AS sales_value
  13. FROM   dual
  14. CONNECT BY level <= 1000;
  15. SQL>COMMIT;
SQL>DROP TABLE sales_tab;
SQL>CREATE TABLE sales_tab (
year_id   NUMBER NOT NULL,
month_id   NUMBER NOT NULL,
day_id   NUMBER NOT NULL,
sales_value NUMBER(10,2) NOT NULL
);
SQL>INSERT INTO sales_tab
SELECT TRUNC(DBMS_RANDOM.value(low => 2010, high => 2012)) AS year_id,
TRUNC(DBMS_RANDOM.value(low => 1, high => 13)) AS month_id,
TRUNC(DBMS_RANDOM.value(low => 1, high => 32)) AS day_id,
ROUND(DBMS_RANDOM.value(low => 1, high => 100), 2) AS sales_value
FROM   dual
CONNECT BY level <= 1000;
SQL>COMMIT;

复习组函数的用法：

copytextpop-up

     
     
  1. SQL>SELECT SUM(sales_value) AS sales_value FROM   sales_tab;
  2. SQL>SELECT year_id, COUNT(*) AS num_rows,
  3. SUM(sales_value) AS sales_value
  4. FROM   sales_tab
  5. GROUP BY year_id
  6. ORDER BY year_id;
  7. SQL>SELECT year_id, month_id,
  8. COUNT(*) AS num_rows,
  9. SUM(sales_value) AS sales_value
  10. FROM   sales_tab
  11. GROUP BY year_id, month_id
  12. ORDER BY year_id, month_id;
SQL>SELECT SUM(sales_value) AS sales_value FROM   sales_tab;
SQL>SELECT year_id, COUNT(*) AS num_rows,
SUM(sales_value) AS sales_value
FROM   sales_tab
GROUP BY year_id
ORDER BY year_id;
SQL>SELECT year_id, month_id,
COUNT(*) AS num_rows,
SUM(sales_value) AS sales_value
FROM   sales_tab
GROUP BY year_id, month_id
ORDER BY year_id, month_id;

图-3 在测试表中使用组函数的运行结果

图-4 在测试表中使用组函数的运行结果

ROLLUP函数的用法：

copytextpop-up

     
     
  1. SELECT year_id, month_id, 
  2. SUM(sales_value) AS sales_value
  3. FROM   sales_tab
  4. GROUP BY 
  5. ROLLUP (year_id, month_id)
  6. ORDER BY year_id, month_id;
SELECT year_id, month_id, 
SUM(sales_value) AS sales_value
FROM   sales_tab
GROUP BY 
ROLLUP (year_id, month_id)
ORDER BY year_id, month_id;

图-5 在测试表中使用ROLLUP函数的运行结果

copytextpop-up

     
     
  1. SELECT year_id, month_id, day_id, SUM(sales_value) AS sales_value
  2. FROM   sales_tab
  3. GROUP BY ROLLUP (year_id, month_id, day_id)
  4. ORDER BY year_id, month_id, day_id;
SELECT year_id, month_id, day_id, SUM(sales_value) AS sales_value
FROM   sales_tab
GROUP BY ROLLUP (year_id, month_id, day_id)
ORDER BY year_id, month_id, day_id;

图-6 在测试表中使用ROLLUP函数的运行结果

1.5.2. CUBE

CUBE函数的语法形式：

copytextpop-up

     
     
  1. GROUP BY CUBE(a, b, c)
GROUP BY CUBE(a, b, c)

对cube的每个参数，都可以理解为取值为参与分组和不参与分组两个值的一个维度，所有维度取值组合的集合就是分组后的集合。对于n个参数的cube，有2^n次分组。

如果GROUP BY CUBE(a,b,c)，首先对(a,b,c)进行GROUP BY，然后依次是(a,b)，(a,c)，(a)，(b,c)，(b)，(c)，最后对全表进行GROUP BY操作，所以一共是2^3=8次分组。

copytextpop-up

     
     
  1. SELECT a,b,c,SUM(d) FROM test GROUP BY CUBE(a,b,c);
SELECT a,b,c,SUM(d) FROM test GROUP BY CUBE(a,b,c);

等价于：

copytextpop-up

     
     
  1. SELECT a,b,c,SUM(d) FROM test GROUP BY a,b,c
  2. UNION ALL
  3. SELECT a,b,NULL,SUM(d) FROM test GROUP BY a,b
  4. UNION ALL
  5. SELECT a,NULL,c,SUM(d) FROM test GROUP BY a,c
  6. UNION ALL
  7. SELECT a,NULL,NULL,SUM(d) FROM test GROUP BY a
  8. UNION ALL
  9. SELECT NULL,b,c,SUM(d) FROM test GROUP BY b,c
  10. UNION ALL
  11. SELECT NULL,b,NULL,SUM(d) FROM test GROUP BY b
  12. UNION ALL
  13. SELECT NULL,NULL,c,SUM(d) FROM test GROUP BY c
  14. UNION ALL
  15. SELECT NULL,NULL,NULL,SUM(d) FROM test ;
SELECT a,b,c,SUM(d) FROM test GROUP BY a,b,c
UNION ALL
SELECT a,b,NULL,SUM(d) FROM test GROUP BY a,b
UNION ALL
SELECT a,NULL,c,SUM(d) FROM test GROUP BY a,c
UNION ALL
SELECT a,NULL,NULL,SUM(d) FROM test GROUP BY a
UNION ALL
SELECT NULL,b,c,SUM(d) FROM test GROUP BY b,c
UNION ALL
SELECT NULL,b,NULL,SUM(d) FROM test GROUP BY b
UNION ALL
SELECT NULL,NULL,c,SUM(d) FROM test GROUP BY c
UNION ALL
SELECT NULL,NULL,NULL,SUM(d) FROM test ;

等价于只是方便理解，其内部运行机制并不相同，其效率远高于UNION ALL。

在sales_value表中使用cube函数：

copytextpop-up

     
     
  1. SELECT year_id, month_id,
  2. SUM(sales_value) AS sales_value
  3. FROM   sales_tab
  4. GROUP BY CUBE (year_id, month_id)
  5. ORDER BY year_id, month_id;
SELECT year_id, month_id,
SUM(sales_value) AS sales_value
FROM   sales_tab
GROUP BY CUBE (year_id, month_id)
ORDER BY year_id, month_id;

图-7在测试表中使用CUBE函数的运行结果

copytextpop-up

     
     
  1. SELECT year_id,  month_id, day_id,
  2. SUM(sales_value) AS sales_value
  3. FROM   sales_tab
  4. GROUP BY CUBE (year_id, month_id, day_id)
  5. ORDER BY year_id, month_id, day_id;
SELECT year_id,  month_id, day_id,
SUM(sales_value) AS sales_value
FROM   sales_tab
GROUP BY CUBE (year_id, month_id, day_id)
ORDER BY year_id, month_id, day_id;

1.5.3. GROUPING SETS

GROUPING SETS 运算符可以生成与使用单个 GROUP BY、ROLLUP 或 CUBE 运算符所生成的结果集相同的结果集，但是使用更灵活。

如果不需要获得由完备的 ROLLUP 或 CUBE 运算符生成的全部分组，则可以使用 GROUPING SETS 仅指定所需的分组。GROUPING SETS 列表可以包含重复的分组。

GROUPING SETS示例：

copytextpop-up

     
     
  1. SELECT year_id, month_id, SUM(sales_value)  
  2. FROM sales_tab
  3. GROUP BY CUBE (year_id,month_id)
  4. order by 1, 2; 
  5.  
  6. SELECT year_id, month_id, SUM(sales_value)  
  7. FROM sales_tab
  8. GROUP BY GROUPING SETS (  (year_id), (month_id))
  9. order by 1, 2
SELECT year_id, month_id, SUM(sales_value)  
FROM sales_tab
GROUP BY CUBE (year_id,month_id)
order by 1, 2; 

SELECT year_id, month_id, SUM(sales_value)  
FROM sales_tab
GROUP BY GROUPING SETS (  (year_id), (month_id))
order by 1, 2

其中分组方式示例如下：

  • 使用GROUP BY GROUPING SETS(a,b,c)，则对(a),(b),(c)进行GROUP BY
  • 使用GROUP BY GROUPING SETS((a,b),c), 则对(a,b),(c)进行GROUP BY
  • GROUPING BY GROUPING SET(a,a) , 则对(a)进行2次GROUP BY, GROUPING SETS的参数允许重复 

1.6. 集合操作

1.6.1. UNION、UNION ALL

图-8 集合操作

为了合并多个SELECT语句的结果，可以使用集合操作符，实现集合的并、交、差。

集合操作符包括UNION、UNION ALL、INTERSECT和MINUS。多条作集合操作的SELECT语句的列的个数和数据类型必须匹配。

ORDER BY子句只能放在最后的一个查询语句中。

集合操作的语法如下：

copytextpop-up

     
     
  1. SELECT statement1
  2. [UNION | UNION ALL | INTERSECT | MINUS]
  3. SELECT statement2;
SELECT statement1
[UNION | UNION ALL | INTERSECT | MINUS]
SELECT statement2;

UNION和UNION ALL用来获取两个或两个以上结果集的并集：

  • UNION操作符会自动去掉合并后的重复记录。
  • UNION ALL返回两个结果集中的所有行，包括重复的行。
  • UNION操作符对查询结果排序，UNION ALL不排序。

例如，合并职位是’MANAGER’的员工和薪水大于2500的员工集合，查看两种方式的结果差别：

copytextpop-up

     
     
  1. --Union
  2. SELECT ename, job, sal FROM emp
  3. WHERE job = 'MANAGER'
  4. SELECT ename, job, sal FROM emp
  5. WHERE sal> 2500;
  6.  
  7. --Union all
  8. SELECT ename, job, sal FROM emp
  9. WHERE job = 'MANAGER'
  10. SELECT ename, job, sal FROM emp
  11. WHERE sal> 2500;
--Union
SELECT ename, job, sal FROM emp
WHERE job = 'MANAGER'
SELECT ename, job, sal FROM emp
WHERE sal> 2500;

--Union all
SELECT ename, job, sal FROM emp
WHERE job = 'MANAGER'
SELECT ename, job, sal FROM emp
WHERE sal> 2500;

1.6.2. INTERSECT

INTERSECT函数获得两个结果集的交集，只有同时存在于两个结果集中的数据，才被显示输出。使用INTERSECT操作符后的结果集会以第一列的数据作升序排列。

例如：显示职位是’MANAGER’的员工和薪水大于2500的员工的交集：

copytextpop-up

     
     
  1. SELECT ename, job, sal FROM emp
  2. WHERE job = 'MANAGER'
  3. INTERSECT
  4. SELECT ename, job, sal FROM emp
  5. WHERE sal> 2500;
SELECT ename, job, sal FROM emp
WHERE job = 'MANAGER'
INTERSECT
SELECT ename, job, sal FROM emp
WHERE sal> 2500;

1.6.3. MINUS

MINUS函数获取两个结果集的差集。只有在第一个结果集中存在，在第二个结果集中不存在的数据，才能够被显示出来。也就是结果集一减去结果集二的结果。

例如：列出职位是MANAGER但薪水低于2500的员工记录：

copytextpop-up

     
     
  1. SELECT ename, job, sal FROM emp
  2. WHERE job = 'MANAGER'
  3. MINUS
  4. SELECT ename, job, sal FROM emp
  5. WHERE sal> 2500;
SELECT ename, job, sal FROM emp
WHERE job = 'MANAGER'
MINUS
SELECT ename, job, sal FROM emp
WHERE sal> 2500;
ALON8686
关注
专栏目录
SQL 聚合函数
m0_71368368的博客
05-25 5908
一.聚合函数 1.sum（）求和 select sum（age）from 库名 2,。count （）统计有多少条记录 select count（*）from 库名 3.avg（） 求平均值 select avg（age）from 库名 4. max （）求最大值 select max（age）from 库名 5.min （）求最小值 select min （age） from 库名 二 .聚合函数和分数函数搭配运用 group by select 列名+聚合函数 from 库名.
SQL高级查询
05-02
### SQL高级查询知识点详解 #### 一、SQL简介与历史背景 - **SQL** 的全称是 **结构化查询语言** (Structural Query Language)，它是一种用于管理关系型数据库的标准编程语言。 - 1986年10月，美国国家标准学会 ...
高级查询
weixin_30652491的博客
08-02 111
高级查询 1、连接查询 select * from info,nation——形成笛卡尔积 会形成一个一一对应 select * from info,nation where info.nation=nation.code select info.code,info.name,sex,nation.name,birthday from info,nation where info.n...
数据库常用的SQL查询语句(非常详细)，看完这一篇就足够了
最新发布
weixin_53249168的博客
08-15 4209
当我们进⾏语句查询的时候，总会遇到我们需要的条件需要通过另⼀个查询语句查询出来后才能进⾏，就是说A 查询语句需要依赖B 查询语句的查询结果，B 查询就是⼦查询，A 查询语句就是主查询，⼀个SQL语句可以包含多个⼦查询。如果查询数据的来源来自多张表，则必须对这些表进行连接查询，连接是把不同表的记录连到一起的最普遍的方法，通过连接查询可将多个表作为一个表进行处理，连接查询分为内连接和外连接。左外连接：是表1和表2的交集再并上表1的其他数据。右外连接：是表1和表2的交集再并上表2的其他数据。
SQL 高级查询
Javayanyang的博客
05-04 778
select *from score select *from Student --统计函数： --count总数、max最大、min最小、avg平均、sum求和 -- count ​ select count(*)from Student ​ -- max select max(sage)from Student -- min ​ select min (sage)from Student ​ --avg ​ select avg(sage)from Student ..
高级SQL查询
qq_52463861的博客
03-14 775
返回查询的数据有多少条。
sql高级查询50题.docx
10-10
"SQL高级查询50题" SQL 高级查询是数据库管理中的一种重要技术，能够帮助开发者和数据库管理员更好地管理和维护数据库。下面是50个 SQL 高级查询题目的知识点总结： 查询“001”课程比“002”课程成绩高的所有...
oracle实验6-sql高级查询.doc
10-29
Oracle实验报告
SQL高级查询技术.doc
01-04
### SQL高级查询技术详解 #### 一、连接查询：整合多表数据的艺术 连接查询是SQL高级查询技术的核心，主要用于从两个或多个表中检索数据，实现数据的整合与关联。这种技术对于处理复杂的数据关系至关重要，特别是...
SQL 高级查询技术
08-10
SQL高级查询技术是数据库管理中不可或缺的一部分，它涵盖了多种复杂操作，使得数据处理更为高效和精确。本章主要探讨三个核心主题：日期和时间处理、层次查询以及分析查询，这些都是Oracle Database 10g系统中重要的...
MySql高级查询
04-27
SqlYog高级查询 In子查询 ..................................
sql高级查询（入门与提高）
12-29
sql高级查询喔 sql高级查询喔 sql高级查询喔 sql高级查询喔 sql高级查询喔
SQL之高级查询
SwmIsMe的博客
12-12 1330
子查询子查询是一条SELECT语句，但它是嵌套其他SQL语句中的 为的是给该SQL提供数据以支持其执行操作查看谁的工资高于CLARK？ SELECT ename,sal FROM emp_swm WHERE sal>( SELECT sal FROM emp_swm WHERE ename='CLARK')查看与CLARK同职位的员工？ SELECT ename,j
【SQL】SQL高级查询
weixin_44727383的博客
05-11 143
student表 take表 查询只选了CS112课程，而没有选其他课程的学生信息； select s.* from student s, take t where s.sno = t.sno and s.sno not in ( select sno from take where cno != 'CS112' ) 使用聚合函数 COUNT 判断哪些学生最多选修了两门课程 from student s, take t where s.sno = t.sno group by s.
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值