当不存在行数据时返回零值
当不存在行数据时返回零值
提问于 2012-08-26 11:10:02
我的问题以前已经解决过，但我似乎无法将任何解决方案应用于我的查询以使其工作。非常感谢你的指导。
下面的查询返回以下数据集：
0-1天300 2-3天6000 3-4天100
SELECT(CASE WHEN time_dtm > SYSDATE -1 THEN '0-1 day'
WHEN time_dtm > SYSDATE -2 AND time_dtm < SYSDATE -1 THEN '1-2 days'
WHEN time_dtm > SYSDATE -3 AND time_dtm < SYSDATE -2 THEN '2-3 days'
WHEN time_dtm > SYSDATE -4 AND time_dtm < SYSDATE -3 THEN '3-4 days'
WHEN time_dtm > SYSDATE -5 AND time_dtm < SYSDATE -4 THEN 'Closed'
END) AS Age,
COUNT( * ) AS "Count"
FROM table_1
WHERE id IN (1,2,3)
GROUP BY (CASE WHEN time_dtm > SYSDATE -1 THEN '0-1 day'
WHEN time_dtm > SYSDATE -2 AND time_dtm < SYSDATE -1 THEN '1-2 days'
WHEN time_dtm > SYSDATE -3 AND time_dtm < SYSDATE -2 THEN '2-3 days'
WHEN time_dtm > SYSDATE -4 AND time_dtm < SYSDATE -3 THEN '3-4 days'
WHEN time_dtm > SYSDATE -5 AND time_dtm < SYSDATE -4 THEN 'Closed'
END)
ORDER BY (CASE WHEN time_dtm > SYSDATE -1 THEN '0-1 day'
WHEN time_dtm > SYSDATE -2 AND time_dtm < SYSDATE -1 THEN '1-2 days'
WHEN time_dtm > SYSDATE -3 AND time_dtm < SYSDATE -2 THEN '2-3 days'
WHEN time_dtm > SYSDATE -4 AND time_dtm < SYSDATE -3 THEN '3-4 days'
WHEN time_dtm > SYSDATE -5 AND time_dtm < SYSDATE -4 THEN 'Closed'
END)但是，我希望它将零/空行显示为零，如下所示：
1-2天0 2-3天6000 3-4天100收盘0
我从过去的几天里读过各种各样的文章: NVL，COALESCE，FULL/左右外接，左/右联接，UNION等等，所有这些都没有案例陈述，而且我自己也试着去解决它！你必须知道什么时候该停下来问路。
回答 1
Stack Overflow用户
回答已采纳
发布于 2012-08-26 11:17:55
首先，重写您的查询。使用视图或通用表表达式，避免对SELECT、GROUP BY、ORDER BY子句重复三次。您的查询变成：
WITH data AS (
SELECT(CASE WHEN time_dtm > SYSDATE -1 THEN '0-1 day'
WHEN time_dtm > SYSDATE -2 AND
time_dtm < SYSDATE -1 THEN '1-2 days'
WHEN time_dtm > SYSDATE -3 AND
time_dtm < SYSDATE -2 THEN '2-3 days'
WHEN time_dtm > SYSDATE -4 AND
time_dtm < SYSDATE -3 THEN '3-4 days'
WHEN time_dtm > SYSDATE -5 AND
time_dtm < SYSDATE -4 THEN 'Closed'
END) AS Age
FROM table_1
WHERE id IN (1,2,3)
)
SELECT Age, COUNT(*)
FROM data
GROUP BY Age
ORDER BY Age然后，为了确保您想要的任何组都在结果中可用，您有很多选项。
你可以用UNION ALL
WITH data AS (
SELECT(CASE WHEN time_dtm > SYSDATE -1 THEN '0-1 day'
WHEN time_dtm > SYSDATE -2 AND
time_dtm < SYSDATE -1 THEN '1-2 days'
WHEN time_dtm > SYSDATE -3 AND
time_dtm < SYSDATE -2 THEN '2-3 days'
WHEN time_dtm > SYSDATE -4 AND
time_dtm < SYSDATE -3 THEN '3-4 days'
WHEN time_dtm > SYSDATE -5 AND
time_dtm < SYSDATE -4 THEN 'Closed'
END) AS Age
FROM table_1
WHERE id IN (1,2,3)
-- The below will add one record for every desired Age group
UNION ALL
SELECT '0-1 day' FROM DUAL UNION ALL
SELECT '1-2 days' FROM DUAL UNION ALL
SELECT '2-3 days' FROM DUAL UNION ALL
SELECT '3-4 days' FROM DUAL UNION ALL
SELECT 'Closed' FROM DUAL
)
SELECT Age, COUNT(*) - 1 -- Subtract the extra record again
FROM data
GROUP BY Age
ORDER BY Age一个完全不同的解决方案将涉及LEFT OUTER JOINs
-- Groups is a dynamic table that contains the date ranges and their "Age" label
WITH groups AS (
SELECT SYSDATE -1 lower, SYSDATE upper, '0-1 day' Age FROM DUAL UNION ALL
SELECT SYSDATE -2 , SYSDATE -1 , '1-2 days' FROM DUAL UNION ALL
SELECT SYSDATE -3 , SYSDATE -2 , '2-3 days' FROM DUAL UNION ALL
SELECT SYSDATE -4 , SYSDATE -3 , '3-4 days' FROM DUAL UNION ALL
SELECT SYSDATE -5 , SYSDATE -4 , 'Closed' FROM DUAL
)
SELECT g.Age, NVL(SUM(t.counter), 0)
FROM groups g
-- LEFT OUTER JOINing "table_1" to "groups" will ensure that every group
-- appears at least once in the result
LEFT OUTER JOIN (
SELECT 1 counter, t.* FROM table_1 t WHERE t.id IN (1,2,3)
) t
ON t.time_dtm >= g.lower
AND t.time_dtm < g.upper
GROUP BY g.Age
ORDER BY g.Age在第二个示例中，您也可以不使用CTE，并为groups表使用嵌套的SELECT。如果您的需求发生了变化，那么很容易看出第二个示例在将来是如何更简单地发展的。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/12129611
复制相关文章
相似问题
腾讯云开发者
