1 以面向集合的思维方式来思考
1.1 从面向过程转变为基于集合的思维方式
hr@orclpdb1:orclcdb>
hr@orclpdb1:orclcdb> select employee_id
2 from job_history j1
where not exists
4 (select null
5 from job_history j2
6 where j2.employee_id = j1.employee_id
7 and round(months_between(j2.start_date, j2.end_date) / 12, 0) <>
8 round(months_between(j1.start_date, j1.end_date) / 12, 0));
EMPLOYEE_ID
-----------
102
114
122
176
176
201
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2055871379
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 400 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN ANTI | | 10 | 400 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| JOB_HISTORY | 10 | 200 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | JHIST_EMPLOYEE_IX | 10 | | 1 (0)| 00:00:01 |
|* 4 | FILTER | | | | | |
|* 5 | SORT JOIN | | 10 | 200 | 4 (25)| 00:00:01 |
| 6 | TABLE ACCESS FULL | JOB_HISTORY | 10 | 200 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter(ROUND(MONTHS_BETWEEN(INTERNAL_FUNCTION("J2"."START_DATE"),INTERNAL_FUNCTION(
"J2"."END_DATE"))/12,0)<>ROUND(MONTHS_BETWEEN(INTERNAL_FUNCTION("J1"."START_DATE"),INTERNA
L_FUNCTION("J1"."END_DATE"))/12,0))
5 - access("J2"."EMPLOYEE_ID"="J1"."EMPLOYEE_ID")
filter("J2"."EMPLOYEE_ID"="J1"."EMPLOYEE_ID")
Statistics
----------------------------------------------------------
154 recursive calls
11 db block gets
194 consistent gets
8 physical reads
2120 redo size
645 bytes sent via SQL*Net to client
894 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
8 sorts (memory)
0 sorts (disk)
6 rows processed
hr@orclpdb1:orclcdb>
hr@orclpdb1:orclcdb> select employee_id
2 from job_history
group by employee_id
having min(round(months_between(start_date,end_date) / 12, 0))=
5 max(round(months_between(start_date,end_date) / 12, 0));
EMPLOYEE_ID
-----------
102
114
122
176
201
5 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2858804740
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 2 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | SORT GROUP BY NOSORT | | 1 | 20 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| JOB_HISTORY | 10 | 200 | 2 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | JHIST_EMP_ID_ST_DATE_PK | 10 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(MIN(ROUND(MONTHS_BETWEEN(INTERNAL_FUNCTION("START_DATE"),INTERNAL_FUNCTION("END_DAT
E"))/12,0))=MAX(ROUND(MONTHS_BETWEEN(INTERNAL_FUNCTION("START_DATE"),INTERNAL_FUNCTION("END_DATE"
))/12,0)))
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
636 bytes sent via SQL*Net to client
547 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5 rows processed
hr@orclpdb1:orclcdb> 
1.2 面向过程与基于集合的思维方式：例子
任务是要计算出一个顾客的各个订单所用的平均天数。
面向过程的思维方式
oe@orclpdb1:orclcdb>
oe@orclpdb1:orclcdb> -- show the list of orders dates for customer 102
oe@orclpdb1:orclcdb> select customer_id,order_date
2 from orders
3 where customer_id = 102;
CUSTOMER_ID ORDER_DATE
----------- ---------------------------------------------------------------------------
102 19-NOV-2007 15:41:54.696211
102 14-SEP-2007 08:53:40.223345
102 29-MAR-2007 13:22:40.536996
102 14-SEP-2006 06:03:04.763452
4 rows selected.
oe@orclpdb1:orclcdb> --Determine the order_date prior to the current row's order_date
oe@orclpdb1:orclcdb> select customer_id,order_date,
2 lag(order_date,1,order_date)
3 over (partition by customer_id order by order_date)
4 as prev_order_date
5 from orders
6 where customer_id = 102;
CUSTOMER_ID ORDER_DATE
----------- ---------------------------------------------------------------------------
PREV_ORDER_DATE
---------------------------------------------------------------------------
102 14-SEP-2006 06:03:04.763452
14-SEP-2006 06:03:04.763452
102 29-MAR-2007 13:22:40.536996
14-SEP-2006 06:03:04.763452
102 14-SEP-2007 08:53:40.223345
29-MAR-2007 13:22:40.536996
102 19-NOV-2007 15:41:54.696211
14-SEP-2007 08:53:40.223345
4 rows selected.
oe@orclpdb1:orclcdb> -- Determine the days between each order
oe@orclpdb1:orclcdb>
oe@orclpdb1:orclcdb> select trunc(order_date) - trunc(prev_order_date) days_between
2 from
3 (
4 select customer_id,order_date,
5 lag(order_date,1,order_date)
6 over(partition by customer_id order by order_date)
7 as prev_order_date
8 from orders
9 where customer_id = 102);
DAYS_BETWEEN
------------
0
196
169
66
4 rows selected.
oe@orclpdb1:orclcdb>
oe@orclpdb1:orclcdb> -- Put it together with an AVG function to get the final answer
oe@orclpdb1:orclcdb> select avg(trunc(order_date) - trunc(prev_order_date)) avg_days_between
2 from
3 (
4 select customer_id,order_date,
5 lag(order_date,1,order_date)
6 over(partition by customer_id order by order_date)
7 as prev_order_date
8 from orders
9 where customer_id = 102);
AVG_DAYS_BETWEEN
----------------
107.75
1 row selected.
oe@orclpdb1:orclcdb> 给出一个按照基于集合的思想方式来写的查询例子：
oe@orclpdb1:orclcdb> select (max(trunc(order_date)) - min(trunc(order_date)))/ count(*) as avg_days_between
2 from orders
3 where customer_id = 102;
AVG_DAYS_BETWEEN
----------------
107.75
1 row selected.
oe@orclpdb1:orclcdb> 2.集合运算
Oracle支持4种集合运算： UNION 、UNION ALL、MINUS、INTERSECT
所有进行集合运算都必须符合下面的条件：
- 所有的输入查询必须返回相同数目的列。
- 每一列的数据类型必须与对应的其他输入查询一致。
- ORDER BY 子句不能在某个单独的查询中应用。
- 列名源自第一个输入查询。
2.1 UNION 和UNION ALL
2.2 MINUS
2.3 INTERSECT
3.集合与空值
空值不是一个值，空值最多就是一个标记。
3.1 空值与非直观结果
使用空值的例子
scott@orclpdb1:orclcdb> -- select all rows from emp table
scott@orclpdb1:orclcdb> select * from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ----------------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-1980 00:00:00 800 20
7499 ALLEN SALESMAN 7698 20-FEB-1981 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-1981 00:00:00 1250 500 30
7566 JONES MANAGER 7839 02-APR-1981 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-1981 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-1981 00:00:00 2850 30
7782 CLARK MANAGER 7839 09-JUN-1981 00:00:00 2450 10
7788 SCOTT ANALYST 7566 19-APR-1987 00:00:00 3000 20
7839 KING PRESIDENT 17-NOV-1981 00:00:00 5000
7844 TURNER SALESMAN 7698 08-SEP-1981 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-1987 00:00:00 1100 20
7900 JAMES CLERK 7698 03-DEC-1981 00:00:00 950 30
7902 FORD ANALYST 7566 03-DEC-1981 00:00:00 3000 20
7934 MILLER CLERK 7782 23-JAN-1982 00:00:00 1300 10
14 rows selected.
scott@orclpdb1:orclcdb> -- select only rows with deptno of 10,20,30
scott@orclpdb1:orclcdb> select * from scott.emp where deptno in (10, 20, 30);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ----------------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-1980 00:00:00 800 20
7499 ALLEN SALESMAN 7698 20-FEB-1981 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-1981 00:00:00 1250 500 30
7566 JONES MANAGER 7839 02-APR-1981 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-1981 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-1981 00:00:00 2850 30
7782 CLARK MANAGER 7839 09-JUN-1981 00:00:00 2450 10
7788 SCOTT ANALYST 7566 19-APR-1987 00:00:00 3000 20
7844 TURNER SALESMAN 7698 08-SEP-1981 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-1987 00:00:00 1100 20
7900 JAMES CLERK 7698 03-DEC-1981 00:00:00 950 30
7902 FORD ANALYST 7566 03-DEC-1981 00:00:00 3000 20
7934 MILLER CLERK 7782 23-JAN-1982 00:00:00 1300 10
13 rows selected.
scott@orclpdb1:orclcdb> -- select only rows with deptno not in 10,20,30
scott@orclpdb1:orclcdb> select * from scott.emp where deptno not in (10, 20, 30);
no rows selected
scott@orclpdb1:orclcdb> -- select only rows with deptno not 10,20,30 or null
scott@orclpdb1:orclcdb> select * from scott.emp where deptno not in (10, 20, 30)
2 or deptno is null;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ----------------------------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-1981 00:00:00 5000
1 row selected.
scott@orclpdb1:orclcdb> 比较和表达式中的空值
scott@orclpdb1:orclcdb>
scott@orclpdb1:orclcdb> select * from scott.emp where deptno is null;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ----------------------------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-1981 00:00:00 5000
1 row selected.
scott@orclpdb1:orclcdb> select * from scott.emp where deptno = null;
no rows selected
scott@orclpdb1:orclcdb> select sal,comm,sal + comm as tot_comp from scott.emp where deptno = 30;
SAL COMM TOT_COMP
---------- ---------- ----------
1600 300 1900
1250 500 1750
1250 1400 2650
2850
1500 0 1500
950
6 rows selected.
scott@orclpdb1:orclcdb> 空值与集合运算
scott@orclpdb1:orclcdb>
scott@orclpdb1:orclcdb> select null from dual
2 union
3 select null from dual;
N
-
1 row selected.
scott@orclpdb1:orclcdb> select null from dual
2 union all
3 select null from dual;
N
-
2 rows selected.
scott@orclpdb1:orclcdb> select null from dual
2 intersect
3 select null from dual;
N
-
1 row selected.
scott@orclpdb1:orclcdb> select null from dual
2 minus
3 select null from dual;
no rows selected
scott@orclpdb1:orclcdb> select 1 from dual
2 union
3 select null from dual;
1
----------
1
2 rows selected.
scott@orclpdb1:orclcdb> select 1 from dual
2 union all
3 select null from dual;
1
----------
1
2 rows selected.
scott@orclpdb1:orclcdb> select 1 from dual
2 intersect
3 select null from dual;
no rows selected
scott@orclpdb1:orclcdb> select 1 from dual
2 minus
3 select null from dual;
1
----------
1
1 row selected.
scott@orclpdb1:orclcdb> 3.空值与GROUP BY 和ORDER BY
SQL> select comm,count(*) ctr
2 from scott.emp
3 group by comm;
COMM CTR
--------- ----------
300.00 1
1400.00 1
10
500.00 1
0.00 1
SQL> select comm,count(*) ctr
2 from scott.emp
3 group by comm
4 order by comm;
COMM CTR
--------- ----------
0.00 1
300.00 1
500.00 1
1400.00 1
10
SQL> select comm,count(*) ctr
2 from scott.emp
3 group by comm
4 order by comm
5 nulls first;
COMM CTR
--------- ----------
10
0.00 1
300.00 1
500.00 1
1400.00 1
SQL> select ename,sal,comm
2 from scott.emp
3 order by comm,ename;
ENAME SAL COMM
---------- --------- ---------
TURNER 1500.00 0.00
ALLEN 1600.00 300.00
WARD 1250.00 500.00
MARTIN 1250.00 1400.00
ADAMS 1100.00
BLAKE 2850.00
CLARK 2450.00
FORD 3000.00
JAMES 950.00
JONES 2975.00
KING 5000.00
MILLER 1300.00
SCOTT 3000.00
SMITH 800.00
14 rows selected
SQL> 4.空值与聚合函数
SQL> select count(*) row_ct,count(comm) comm_ct,
2 avg(comm) avg_comm, min(comm) min_comm,
3 max(comm) max_comm, sum(comm) sum_comm
4 from scott.emp;
ROW_CT COMM_CT AVG_COMM MIN_COMM MAX_COMM SUM_COMM
---------- ---------- ---------- ---------- ---------- ----------
14 4 550 0 1400 2200
SQL> 
扫一扫
228
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
