合并、nvl和CASE不适用于子查询中的count(*)

Question

我对CTE有一个查询，我希望空行为0。

我想知道为什么合并(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

您正在执行LEFT dte到tmp的连接，因此可以为不匹配的行获取null。

也许您认为，通过使用COALESCE()和NVL() is tmp，最终结果将显示0而不是null，但这是不对的。

COALESCE()和NVL()在tmp中是内部的，如果您只从tmp中选择，则不会得到null，但是由于您这样做了LEFT联接，并且有不匹配的行，那么这些不匹配行的列将由null表示。

因此，如果要删除这些COALESCE()语句，则必须在最后的SELECT语句中使用null和/或null。