SQL-填写没有结果的日期

小编典典

SQL-填写没有结果的日期

sql

我有这个查询：

Select Trunc(Create_Dtime),Count(Trunc(Create_Dtime)) as Day_0 From Player
Group By Trunc(Create_Dtime)
Order By Trunc(Create_Dtime) Asc

它给了我追溯日期，但是如果日期没有任何结果，则将其跳过。我想填写从12年8月5日到现在的所有日期，如果那几天没有任何值，则结果中应为0。

阅读 180

2021-05-16

共1个答案

小编典典

根据您想要创建它们的方式，您可以创建一个日历表或使用Oracleconnect by语法动态生成行。

with the_dates as (
  select max(trunc(Create_Dtime)) as max_date
       , min(trunc(Create_Dtime)) as min_date
    from player 
         )
  , generator as (
  select min_date + level as the_date
    from the_dates
 connect by level <= max_date
         )
select g.the_date, count(trunc(p.Create_Dtime))
  from generator g
  left outer join player p
    on g.the_date = trunc(p.Create_Dtime)
 group by g.the_date
 order by g.the_date desc

如果您选择了“压光机表格”选项，则它会更清洁一些：

with the_dates as (
  select max(trunc(Create_Dtime)) as max_date
       , min(trunc(Create_Dtime)) as min_date
    from player 
         )
select c.the_date, count(trunc(p.Create_Dtime))
  from calender c
  join the_dates td
    on c.the_date between td.min_date and td.max_date
  left outer join join player p
    on c.the_date = trunc(p.Create_Dtime)
 group by c.the_date
 order by c.the_date

或者，刚刚注意到您的日期限制：

with the_dates as (
  select to_date('07-05-2012','dd-mm-yyyy') + level as the_date
    from dual
 connect by level <= trunc(to_date('07-05-2012','dd-mm-yyyy') - sysdate)
         )
 select td.the_date, count(trunc(p.create_dtime))
   from the_dates td
   left outer join player p
     on td.the_date = trunc(p.create_dtime)
  group by td.the_date
  order by td.the_date

对于所有这些，我建议trunc(create_dtime)在您的player表上建立一个索引。

2021-05-16