即使没有数据也显示表中的数据！！甲骨文 [英] Show data from table even if there is no data!! Oracle

问题描述

我有一个查询，显示根据日期收到的消息数.例如:

I have a query which shows count of messages received based on dates. For Eg:

1 | 1-May-2012 
3 | 3-May-2012 
4 | 6-May-2012 
7 | 7-May-2012 
9 | 9-May-2012 
5 | 10-May-2012 
1 | 12-May-2012

正如您在某些日期看到的那样，没有收到任何消息.我想要的是它应该显示所有日期，如果没有收到消息，它应该像这样显示 0

As you can see on some dates there are no messages received. What I want is it should show all the dates and if there are no messages received it should show 0 like this

1 | 1-May-2012
0 | 2-May-2012
3 | 3-May-2012
0 | 4-May-2012
0 | 5-May-2012
4 | 6-May-2012
7 | 7-May-2012
0 | 8-May-2012
9 | 9-May-2012
5 | 10-May-2012
0 | 11-May-2012
1 | 12-May-2012

当表中没有行时，我该如何实现?

How can I achieve this when there are no rows in the table?

推荐答案

为此您不需要单独的表，您可以在查询中创建您需要的内容.这适用于五月:

You don't need a separate table for this, you can create what you need in the query. This works for May:

WITH month_may AS (
    select to_date('2012-05-01', 'yyyy-mm-dd') + level - 1 AS the_date
    from dual
    connect by level < 31
)
SELECT *
  FROM month_may mm
  LEFT JOIN mytable t ON t.some_date = mm.the_date

日期范围将取决于您想要执行此操作的具体方式以及您的范围.

The date range will depend on how exactly you want to do this and what your range is.

这篇关于即使没有数据也显示表中的数据！！甲骨文的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！