coalize、nvl和CASE对于子查询中的count(*)不起作用

Question

我有一个CTE查询，NULL行需要0。

我想知道为什么COALESCE(tsting(、NVL(tsting2(和CASE(rw_cnt(不工作？我遗漏了什么吗？

with dte AS(Select store, date_id from store cross JOIN (SELECT 
(TRUNC(sysdate)-1 + (LEVEL-10)) AS DATE_ID
FROM DUAL connect by level <=( (sysdate-2)-(sysdate-10)))
store<10)
, tmp as(Select calendar_dt, str, CASE WHEN rw_cnt IS NULL THEN 0 ELSE rw_cnt 
END rw_cnt, COALESCE(rw_cnt, 0) tsting, NVL(rw_cnt, 0 ) tsting2
from (SELECT calendar_dt, str,  count(*) rw_cnt FROM table2 group by calendar_dt, str))
Select store, date_id, rw_cnt, case when rw_cnt IS NULL THEN 0 ELSE rw_cnt END testing,
tsting, tsting2 from dte LEFT OUTER JOIN tmp ON dte.date_id = tmp.calendar_dt 
AND dte.store = temp.store
order by store, date_id;

我得到的数据如下：

+-------+-----------+--------+---------+--------+---------+
| STORE | DATE_ID   | RW_CNT | TESTING | TSTING | TSTING2 |
+-------+-----------+--------+---------+--------+---------+
| 3     | 18-OCT-20 | NULL   | 0       | NULL   | NULL    |
+-------+-----------+--------+---------+--------+---------+
| 3     | 19-OCT-20 | 73     | 73      | 73     | 73      |
+-------+-----------+--------+---------+--------+---------+
| 3     | 20-OCT-20 | 88     | 88      | 88     | 88      |
+-------+-----------+--------+---------+--------+---------+
| 3     | 21-OCT-20 | 63     | 63      | 63     | 63      |
+-------+-----------+--------+---------+--------+---------+
| 3     | 22-OCT-20 | 100    | 100     | 100    | 100     |
+-------+-----------+--------+---------+--------+---------+
| 3     | 23-OCT-20 | 105    | 105     | 105    | 105     |
+-------+-----------+--------+---------+--------+---------+
| 3     | 24-OCT-20 | 36     | 36      | 36     | 36      |
+-------+-----------+--------+---------+--------+---------+
| 3     | 25-OCT-20 | 3      | 3       | 3      | 3       |
+-------+-----------+--------+---------+--------+---------+
| 4     | 18-OCT-20 | NULL   | 0       | NULL   | NULL    |
+-------+-----------+--------+---------+--------+---------+
| 4     | 19-OCT-20 | 30     | 30      | 30     | 30      |
+-------+-----------+--------+---------+--------+---------+
| 4     | 20-OCT-20 | 24     | 24      | 24     | 24      |
+-------+-----------+--------+---------+--------+---------+
| 4     | 21-OCT-20 | 38     | 38      | 38     | 38      |
+-------+-----------+--------+---------+--------+---------+
| 4     | 22-OCT-20 | 21     | 21      | 21     | 21      |
+-------+-----------+--------+---------+--------+---------+
| 4     | 23-OCT-20 | 37     | 37      | 37     | 37      |
+-------+-----------+--------+---------+--------+---------+
| 4     | 24-OCT-20 | 3      | 3       | 3      | 3       |
+-------+-----------+--------+---------+--------+---------+
| 4     | 25-OCT-20 | NULL   | 0       | NULL   | NULL    |
+-------+-----------+--------+---------+--------+---------+

欢迎对查询优化提出任何建议…：(

Answer 1

您正在执行dte到tmp的LEFT联接，因此您将获得不匹配行的nulls

也许您认为在tmp内部使用COALESCE()和NVL()，最终结果将显示0而不是null，但这是不对的
COALESCE()和NVL()在tmp中是内部的，如果仅从tmp中选择，则不会获得nulls，但由于执行了LEFT联接，并且存在不匹配的行，因此这些不匹配行的列将由nulls表示。

因此，如果您想删除这些nulls，则必须在最终的SELECT语句中使用COALESCE()和/或NVL()。