当DAY，MONTH，YEAR分组时，sql缺少行 [英] sql missing rows when grouped by DAY, MONTH, YEAR

问题描述

如果我从一个表组中按月，日，年
进行选择，那么它只返回具有记录的行，并且没有任何记录而丢弃组合，这样一来，每天或每个月都有活动，你必须积极地查看日期栏的差距。在T-SQL中，即使没有数据，每天/每月也可以获得一行？

If I select from a table group by the month, day, year, it only returns rows with records and leaves out combinations without any records, making it appear at a glance that every day or month has activity, you have to look at the date column actively for gaps. How can I get a row for every day/month/year, even when no data is present, in T-SQL?

推荐答案

我的开发人员用这段代码回覆了我，下划线被转换成破折号，因为StackOverflow被压缩了下划线 - 不需要号码表。我们的例子很复杂，加入到另一个表中，但也许代码示例有助于某人有所帮助。

My developer got back to me with this code, underscores converted to dashes because StackOverflow was mangling underscores -- no numbers table required. Our example is complicated a bit by a join to another table, but maybe the code example will help someone someday.

declare @career-fair-id int 
select @career-fair-id = 125

create table #data ([date] datetime null, [cumulative] int null) 

declare @event-date datetime, @current-process-date datetime, @day-count int 
select @event-date = (select careerfairdate from tbl-career-fair where careerfairid = @career-fair-id) 
select @current-process-date = dateadd(day, -90, @event-date) 

    while @event-date <> @current-process-date 
    begin 
    select @current-process-date = dateadd(day, 1, @current-process-date) 
    select @day-count = (select count(*) from tbl-career-fair-junction where attendanceregister <= @current-process-date and careerfairid = @career-fair-id) 
        if @current-process-date <= getdate() 
        insert into #data ([date], [cumulative]) values(@current-process-date, @day-count) 
    end 

    select * from #data 
    drop table #data 

这篇关于当DAY，MONTH，YEAR分组时，sql缺少行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！