如果没有行返回其他表中的连接条件值，则选择默认行

Question

我有两个表ORDERS和RATE：

CREATE TABLE ORDERS
(   ID NUMBER(12,0), 
DOW NUMBER(12,0), 
COUNT NUMBER(12,0)
);
CREATE TABLE RATE
(   ID NUMBER(12,0), 
DOW NUMBER(12,0), 
RATE NUMBER(12,0)
);
INSERT INTO ORDERS (ID, DOW, COUNT) VALUES ('1', '1', '5');
INSERT INTO ORDERS (ID, DOW, COUNT) VALUES ('1', '3', '7');
INSERT INTO ORDERS (ID, DOW, COUNT) VALUES ('2', '1', '2');
INSERT INTO RATE (ID, DOW, RATE) VALUES ('1', '1', '10');
INSERT INTO RATE (ID, DOW, RATE) VALUES ('1', '2', '20');
INSERT INTO RATE (ID, DOW, RATE) VALUES ('1', '0', '50');

我想获取表 ORDERS 中所有订单的成本，假设默认汇率为 10 用于那些在 RATE 表中没有相应 ID 的费率的订单，并且如果 id 在 RATE 表中可用，但没有该 id 的某个道琼斯的费率可用， DOW 0 的默认汇率始终可用于该 ID。

对于上面的示例，预期会出现以下情况：

id  dow   cost
--------------
1    1     50
1    3     350
2    1     20

请注意，id=1 和 dow = 3 的 350 来自计数 7 乘以 id=1 和 dow=0 的比率 50，因为表中没有 id=1 和 dow=3 的比率。我尝试了以下方法，但没有运气：

SELECT o.id, o.dow, o.count*(coalesce(r.rate, 10)) as cost
FROM orders o
left join rate r
on r.id = o.id
and  r.dow = o.dow
;

并且以下内容存在编译错误：

ORA-00904："O"."DOW"：标识符无效

904. 00000 - "%s： 无效标识符">

SELECT o.id ,o.dow ,o.count * (coalesce(r.rate, 10)) cost
FROM orders o
LEFT JOIN (
SELECT id, dow, rate
FROM rate
WHERE id = o.id
AND dow = o.dow

UNION ALL

SELECT id ,dow ,rate
FROM rate
WHERE id = o.id
AND dow = 0
AND NOT EXISTS (
SELECT 1
FROM rate
WHERE id = o.id
AND dow = o.dow
)
) r
ON r.id = o.id
;

有人可以帮忙查询吗？

Answer 1

您可以将多个outer joins与RATE表一起使用，以获取所需的结果，如下所示：

SQL> SELECT
2      O.ID,
3      O.DOW,
4      O.COUNT * COALESCE(R.RATE, RD.RATE) AS COST
5  FROM
6      ORDERS O
7      LEFT JOIN RATE R ON ( O.DOW = R.DOW )
8      LEFT JOIN RATE RD ON ( R.ID IS NULL
9                             AND RD.DOW = 0 )
10  ORDER BY O.ID;
ID        DOW       COST
---------- ---------- ----------
1          1         50
1          3        350
2          1         20
SQL>

干杯！！