关于tsql:按DAY,MONTH,YEAR分组时,sql缺少行

关于tsql:按DAY,MONTH,YEAR分组时,sql缺少行

sql missing rows when grouped by DAY, MONTH, YEAR

如果我按月,日,年从表组中选择,
它仅返回带有记录的行,而忽略不包含任何记录的组合,从而使您一目了然地每天或每个月都有活动,因此必须主动查看日期列以查找差距。在T-SQL中,即使没有数据,我如何每天/每月/每年都获得一行?


创建日历表并在该表上进行外部联接


我的开发人员使用此代码返回我,下划线转换为破折号,因为StackOverflow正在处理下划线-不需要数字表。我们的示例由于连接到另一个表而变得有些复杂,但是也许该代码示例有一天会帮助某个人。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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

该任务要求将完整的日期集左连接到您的数据上,例如


DECLARE @StartInt int
DECLARE @Increment int
DECLARE @Iterations int

SET @StartInt = 0
SET @Increment = 1
SET @Iterations = 365

SELECT
tCompleteDateSet.[Date]
,AggregatedMeasure = SUM(ISNULL(t.Data, 0))
FROM
(
SELECT
[Date] = dateadd(dd,GeneratedInt, @StartDate)
FROM
[dbo].[tvfUtilGenerateIntegerList] (
@StartInt,
,@Increment,
,@Iterations
)
) tCompleteDateSet
LEFT JOIN tblData t
ON (t.[Date] = tCompleteDateSet.[Date])
GROUP BY
tCompleteDateSet.[Date]

其中表值函数tvfUtilGenerateIntegerList定义为


-- Example Inputs

-- DECLARE @StartInt int
-- DECLARE @Increment int
-- DECLARE @Iterations int
-- SET @StartInt = 56200
-- SET @Increment = 1
-- SET @Iterations = 400
-- DECLARE @tblResults TABLE
-- (
-- IterationId int identity(1,1),
-- GeneratedInt int
-- )

-- =============================================
-- Author: 6eorge Jetson
-- Create date: 11/22/3333
-- Description: Generates and returns the desired list of integers as a table
-- =============================================
CREATE FUNCTION [dbo].[tvfUtilGenerateIntegerList]
(
@StartInt int,
@Increment int,
@Iterations int
)
RETURNS
@tblResults TABLE
(
IterationId int identity(1,1),
GeneratedInt int
)
AS
BEGIN

DECLARE @counter int
SET @counter= 0
WHILE (@counter < @Iterations)
BEGIN
INSERT @tblResults(GeneratedInt) VALUES(@StartInt + @counter*@Increment)
SET @counter = @counter + 1
END

RETURN
END
--Debug
--SELECT * FROM @tblResults


基于SQLMenace所说的内容,您可以使用CROSS JOIN快速填充表或在内存中有效地创建表。
http://www.sitepoint.com/forums/showthread.php?t=562806


使用数字表。尽管这可能有点骇人听闻,但这是我快速查询丢失的数据,显示所有日期或要检查范围内值的任何方法的最佳方法,无论是否使用了该范围内的所有值。铅>


推荐阅读