Mastering Oracle SQL学习笔记(join句法专题第五部份)-优快云博客

本文介绍Oracle 10g中分区外部连接的使用方法，通过示例展示了如何利用此特性显示不同员工在特定年份每个月的开支情况。

2.4.2 Partition Outer Joins

Partition outer joins 是Oracle10g中令人感兴趣的的特性。它可以将数据分成组，句体我也说不清，看了下面的例子你就明白了：

(表employee_expanse)

SELECT * FROM employee_expense;

EMP_ID YEAR MONTH EXPENSE_CLAIM APPROVED_AMT PAID_DATE

---------- ---------- ---------- ------------- ------------ ---------

7369 2002 2 3072.43 3072.43 03-MAR-02

7369 2002 4 30 30 01-JUN-02

7369 2002 5 235.03 35.03 01-JUN-02

7369 2002 9 5095.98 5095.08 31-OCT-02

7369 2002 12 1001.01 1001.01 01-FEB-03

7782 2002 1 111.09 111.09 01-FEB-02

7782 2002 3 9.85 9.85 01-APR-02

7782 2002 7 3987.32 3987.32 01-AUG-02

7782 2002 9 1200 1200 01-OCT-02

（表months）

SELECT * FROM months WHERE year = 2002;

YEAR MONTH

---------- ----------

2002 1

2002 2

2002 3

2002 4

2002 5

2002 6

2002 7

2002 8

2002 9

2002 10

2002 11

2002 12

你可以用month表中的反回的12行，做一个left outer join 把某个员工(employee)每月的expense显示出来：

SELECT NVL(ee.emp_id, 7782), m.year, m.month, NVL(ee.expense_claim,0)

FROM (SELECT * FROM months WHERE year = 2002) m

LEFT OUTER JOIN (SELECT *

FROM employee_expense

WHERE emp_id = 7782) ee

ON m.year = ee.year AND m.month = ee.month

ORDER BY m.month;

（NVL（value,0）该函数的意思是当value的值为空时，以0代替）

NVL(EE.EMP_ID,7782) YEAR MONTH NVL(EE.EXPENSE_CLAIM,0)

------------------- ---------- ---------- -----------------------

7782 2002 1 111.09

7782 2002 2 0

7782 2002 3 9.85

7782 2002 4 0

7782 2002 5 0

7782 2002 6 0

7782 2002 7 3987.32

7782 2002 8 0

7782 2002 9 1200

7782 2002 10 0

7782 2002 11 0

我们可以看到emp_id 为 7782的员工在2002今天各个月份的EXPENSE_CLAIM 被显示出来了，那么如果我人要显示每个员工的在2002今天各个月份的EXPENSE_CLAIM么怎么办，那么Partition Outer Joins 就起作用了：

SELECT ee.emp_id, m.year, m.month, NVL(ee.expense_claim,0)

FROM (SELECT * FROM months WHERE year = 2002) m

LEFT OUTER JOIN employee_expense ee

PARTITION BY (ee.emp_id)

ON m.year = ee.year AND m.month = ee.month

ORDER BY ee.emp_id, m.month;

EMP_ID YEAR MONTH NVL(EE.EXPENSE_CLAIM,0)

---------- ---------- ---------- -----------------------

7369 2002 1 0

7369 2002 2 3072.43

7369 2002 3 0

7369 2002 4 30

7369 2002 5 235.03

7369 2002 6 0

7369 2002 7 0

7369 2002 8 0

7369 2002 9 5095.98

7369 2002 10 0

7369 2002 11 0

7369 2002 12 1001.01

7782 2002 1 111.09

7782 2002 2 0

7782 2002 3 9.85

7782 2002 4 0

7782 2002 5 0

7782 2002 6 0

7782 2002 7 3987.32

7782 2002 8 0

7782 2002 9 1200

7782 2002 10 0

7782 2002 11 0

7782 2002 12 0

上面那种写法我们也可以写成，下面这样（把分别每个员工在2002年各月份EXPENSE_CLAIM取出，再连接起来）：

SELECT NVL(ee.emp_id, 7369), m.year, m.month, NVL(ee.expense_claim,0)

FROM (SELECT * FROM months WHERE year = 2002) m

LEFT OUTER JOIN (SELECT *

FROM employee_expense

WHERE emp_id = 7369) ee

ON m.year = ee.year AND m.month = ee.month

ORDER BY m.month

UNION ALL

SELECT NVL(ee.emp_id, 7782), m.year, m.month, NVL(ee.expense_claim,0)

FROM (SELECT * FROM months WHERE year = 2002) m

LEFT OUTER JOIN (SELECT *

FROM employee_expense

WHERE emp_id = 7782) ee

ON m.year = ee.year AND m.month = ee.month

ORDER BY m.month;

转载于:https://blog.51cto.com/longsoft/82040