Oracle SQL - CONNECT BY语句Where条件中不能使用OR？[已解决]
- 数据
SQL> SELECT * FROM demo_a;CUSTOMER TOTAL
---------- ----------
A 100200SQL> SELECT * FROM demo_b;CUSTOMER RN QTY
---------- ---------- ----------
A 1 30
A 2 60
A 3 101 1902 10- 目标
以CUST字段内容关联A、B两表：相等或同时为空，并按B表RN字段的顺序累计QTY数值，截止到不超过A表对应TOTAL值的行
- 问题
先将两表关联起来
SQL> SELECT *2 FROM demo_a a,3 (SELECT t.*, SUM(qty) over(PARTITION BY customer ORDER BY rn) sum_qty4 FROM demo_b t) b5 WHERE (a.customer IS NULL AND b.customer IS NULL OR6 a.customer = b.customer)7 ;CUSTOMER TOTAL CUSTOMER RN QTY SUM_QTY
---------- ---------- ---------- ---------- ---------- ----------
A 100 A 1 30 30
A 100 A 2 60 90
A 100 A 3 10 100200 1 190 190200 2 10 200整理得到递归条件为：当SUM_QTY小于TOTAL时，则RN应当加1
SQL> SELECT *2 FROM demo_a a,3 (SELECT t.*, SUM(qty) over(PARTITION BY customer ORDER BY rn) sum_qty4 FROM demo_b t) b5 WHERE (a.customer IS NULL AND b.customer IS NULL OR6 a.customer = b.customer)7 START WITH b.rn = 18 CONNECT BY (PRIOR a.customer IS NULL AND a.customer IS NULL OR9 PRIOR a.customer = a.customer)10 AND PRIOR b.sum_qty < PRIOR a.total11 AND b.rn = PRIOR b.rn + 112 ;CUSTOMER TOTAL CUSTOMER RN QTY SUM_QTY
---------- ---------- ---------- ---------- ---------- ----------
没有结果，若是将Where条件改为a.customer = b.customer则可正常得出结果，但这会漏掉CUST为空的数据。难道递归查询中不可以使用OR来关联表吗
- 原因
观察执行计划
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| |
|* 1 | FILTER | | | | | |
|* 2 | CONNECT BY NO FILTERING WITH START-WITH| | | | | |
|* 3 | HASH JOIN | | 5 | 430 | 5 (20)| 00:00:01 |
| 4 | TABLE ACCESS FULL | DEMO_A | 2 | 60 | 2 (0)| 00:00:01 |
| 5 | VIEW | | 5 | 280 | 3 (34)| 00:00:01 |
| 6 | WINDOW SORT | | 5 | 215 | 3 (34)| 00:00:01 |
| 7 | TABLE ACCESS FULL | DEMO_B | 5 | 215 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------1 - filter(("A"."CUSTOMER" IS NULL AND "B"."CUSTOMER" IS NULL))2 - filter("B"."RN"=1)3 - access("A"."CUSTOMER"="B"."CUSTOMER")可见两表关联处(id=3)仅使用了谓词：a.customer=b.customer，而a.customer is null and b.customer is null则成为了最终结果集的一个筛选谓词(id=1)
究其原因，在递归查询的Where条件中，最先执行的是表关联条件，其它筛选条件则是在递归后执行。此处所指的表关联条件必须是同时出现两个表的表达式，a.customer is null and b.customer is null由于仍可分为两个表达式，而每个表达式中只出现了一个表，故作为了最后执行的筛选条件
此例程中表关联条件可以使用NVL处理以使两表同时出现
SQL> SELECT *2 FROM demo_a a,3 (SELECT t.*, SUM(qty) over(PARTITION BY customer ORDER BY rn) sum_qty4 FROM demo_b t) b5 WHERE nvl(a.customer, 'NULL') = nvl(b.customer, 'NULL')6 START WITH b.rn = 17 CONNECT BY (PRIOR a.customer IS NULL AND a.customer IS NULL OR8 PRIOR a.customer = a.customer)9 AND PRIOR b.sum_qty < PRIOR a.total10 AND b.rn = PRIOR b.rn + 111 ;CUSTOMER TOTAL CUSTOMER RN QTY SUM_QTY
---------- ---------- ---------- ---------- ---------- ----------
A 100 A 1 30 30
A 100 A 2 60 90
A 100 A 3 10 100200 1 190 190200 2 10 200