SQL 的默认行

debugcn 发表于 Dev

雷恩

数据库 - 甲骨文

create table customer_exercise(
customer_id number,
exercise_id number,
cnt number,
exercise_date date)

数据

1000    10  3   14-AUG-17
1001    20  6   14-AUG-17
1000    20  2   14-AUG-17

当 in 子句中的条件不存在记录时，是否可以获得默认行？

select customer_id, exercise_id, sum(cnt)
from customer_exercise 
where customer_id in (1000, 1001, 1003)
  and exercise_id in (10, 20)
group by customer_id, exercise_id
order by sum(cnt)

上述查询的结果-

1000    20  2
1000    10  3
1001    20  6

由于 in 子句中的客户 ID 可能没有某些练习 ID 的记录，是否有可能使用 SQL 获得如下结果，其中 sum 为 0？例如，1001 没有运动 id=10 的记录，所以 sum 将为 0。

1001    10  0
1003    10  0
1003    20  0
1000    20  2
1000    10  3
1001    20  6

亚历克斯·普尔

你可以把你的in子句条件变成集合（比如内置的集合类型，对这类事情很方便），将它们扩展为 CTE 中的关系数据，然后交叉连接它们；并左连接到真实表以查看匹配的内容：

with customer_cte (customer_id) as (
  select * from table(sys.odcinumberlist(1000, 1001, 1003))
),
exercise_cte (exercise_id) as (
  select * from table(sys.odcinumberlist(10, 20))
)
select c.customer_id, e.exercise_id, coalesce(sum(ce.cnt), 0) as total_cnt
from customer_cte c
cross join exercise_cte e
left join customer_exercise ce
on ce.customer_id = c.customer_id
and ce.exercise_id = e.exercise_id
group by c.customer_id, e.exercise_id
order by coalesce(sum(cnt), 0), customer_id, exercise_id
/

CUSTOMER_ID EXERCISE_ID  TOTAL_CNT
----------- ----------- ----------
       1001          10          0
       1003          10          0
       1003          20          0
       1000          20          2
       1000          10          3
       1001          20          6

6 rows selected.

如果您已经有单独的customer和exercise表，并且它们至少包含您要查找的所有 ID，那么您可以直接使用它们，并针对它们而不是映射表进行过滤：

select c.customer_id, e.exercise_id, coalesce(sum(ce.cnt), 0) as total_cnt
from customer c
cross join exercise e
left join customer_exercise ce
on ce.customer_id = c.customer_id
and ce.exercise_id = e.exercise_id
where c.customer_id in (1000, 1001, 1003)
and e.exercise_id in (10, 20)
group by c.customer_id, e.exercise_id
order by coalesce(sum(cnt), 0), customer_id, exercise_id

您不会以这种方式获得任何不存在于customer和exercise表中的 ID 的默认行，但这可能不是问题。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。