我的问题先前已得到解决,但我似乎无法对我的查询应用任何解决方案以使其正常工作.非常感谢您提供一些指导.
My question has been addressed previously but I can't seem to apply any solution to my query to make it work. Would very much appreciate some guidance.
我下面的当前查询返回此数据集:
My current query below returns this data set:
| Age | Count | 0-1 day 300 2-3 days 6000 3-4 days 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)
但是,我希望将零/空行显示为零,如下所示:
However, I would like it to show the zero/null rows as zeros like this:
| Age | Count | 0-1 day 300 1-2 days 0 2-3 days 6000 3-4 days 100 Closed 0
在过去的几天里,我已经阅读了各种各样的内容,例如:NVL,COALESCE,FULL/LEFT/RIGHT OUTER JOIN,LEFT/RIGHT JOINS,UNION ALL等都没有CASE语句,并且自己尝试着解决但!您必须知道什么时候停下来并问路.
I've read all sorts from the past couple of days re: NVL, COALESCE, FULL/LEFT/RIGHT OUTER JOIN, LEFT/RIGHT JOINS, UNION ALL etc none of which had CASE statements and tried to work around it myself BUT! You have to know when to stop and ask for directions.
推荐答案首先,重新编写您的查询.使用视图或公用表表达式可以避免对SELECT,GROUP BY,ORDER BY子句重复三遍.您的查询将变为:
First off, re-write your query. Use views or common table expression to avoid repeating yourself three times for your SELECT, GROUP BY, ORDER BY clauses. Your query becomes:
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然后,为了确保您想要的任何组都可以在结果中使用,您有很多选择.
Then, in order to be sure that any of your desired groups will be available in the result, you have lots of options.
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.很容易看出,如果您的需求发生变化,那么第二个示例在将来会变得更简单.
In the second example, you could also do without a CTE and use a nested SELECT for the groups table. It is easy to see how the second example is simpler to evolve in the future, should your requirements change.
更多推荐
没有行数据时返回零值
发布评论