SQL-填写没有结果的日期 [英] SQL -- Filling in Dates that don't have results
问题描述
我有这个查询:
Select Trunc(Create_Dtime),Count(Trunc(Create_Dtime)) as Day_0 From Player
Group By Trunc(Create_Dtime)
Order By Trunc(Create_Dtime) Asc
它给我回溯日期,但是如果一个日期没有任何结果,则将其跳过.我想填写从8-05-12到现在的所有日期,如果那几天没有任何值,则结果中应为0.
It gives me back dates but if a date doesn't have any results, that is skipped. I'd like to fill in all dates from 8-05-12 to present, and if those days don't have any values, just have a 0 in the results.
推荐答案
根据您要创建它们的方式,您可以创建 connect by
语法动态生成行
Depending on how you want to create them you can either create a calender table or generate the rows dynamically using the Oracle connect by
syntax.
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
如果您选择压光机表格"选项,它会更干净:
If you go down the calender table option it's a little bit cleaner:
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
或者,刚注意到您的日期限制:
Or, having just noticed your date constraints:
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
对于所有这些,我建议在您的player
表上的trunc(create_dtime)
上建立一个索引.
For all of these I'd recommend an index on trunc(create_dtime)
on your player
table.
这篇关于SQL-填写没有结果的日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!