代码之家 › 专栏 › 技术社区 › Vishal

为两个日期之间的字段添加缺失值的SQL查询?

sql-server-2008 sql

Vishal · 技术社区 · 14 年前

BioUser- UserId,Weight,DateAdded
DimDate-Date // It has basically all the dates for any period..its basically a table with all dates till 2050

生物使用者-

UserId Weight  DateAdded
 1      178    10/12/2009
 1      175    10/18/2009
 1      172    10/27/2009

Weight Date

178  10/12/2009
178  10/13/2009
178  10/14/2009
178  10/15/2009
178  10/16/2009
178  10/17/2009
175  10/18/2009
175  10/19/2009
175  10/20/2009
175  10/21/2009
175  10/22/2009
175  10/23/2009
175  10/24/2009
175  10/25/2009
175  10/26/2009
172  10/27/2009
172  10/28/2009
172  10/29/2009
172  10/30/2009

我有类似的问题-

Select Weight,DateAdded from BioUser join Dimdate on BioUser.DateAdded=Dimdate.Date

但是上面的方法不起作用,我想从BioUser得到最新的weight条目,而且只有一个条目,因为BioUser表可以有多个条目白天。可以吗谁能帮帮我。。

5 回复 | 直到 14 年前

Thomas 14 年前

我在这里只包含参数来说明您需要在哪里指定最早的日期和最晚的日期。如果需要的话,这两个值可以从你的数据中推断出来。

这里的技巧是将源数据转换为具有开始和结束日期的范围。然后,我们生成一个包含所需时间段的连续日期的日历表,并连接到我们的范围以确定结果。

Declare @MinDate datetime;
Declare @MaxDate datetime;

Set @MinDate = '2009-10-12';
Set @MaxDate = '2009-10-30';

With BioUser As
    (
    Select 1 As UserId, 178 As Weight, '2009-10-12' As DateAdded
    Union All Select 1, 175, '2009-10-18'   
    Union All Select 1, 172, '2009-10-27'   
    )
    , Calendar As
    (
    Select @MinDate As [Date]
    Union All
    Select DateAdd(d,1,[Date])
    From Calendar
    Where [Date] < @MaxDate
    )
    , BioUserDateRanges As
    (
    Select B1.UserId, B1.Weight, B1.DateAdded As StartDate, Coalesce(Min(B2.DateAdded),@MaxDate) As EndDate
    From BioUser As B1
        Left Join BioUser As B2
            On B2.UserId = B1.UserId
                And B2.DateAdded > B1.DateAdded
    Group By B1.UserId, B1.Weight, B1.DateAdded
    )
Select BR.Weight, C.[Date]
From Calendar As C
    Join BioUserDateRanges As BR
        On BR.StartDate <= C.[Date]
            And BR.EndDate >= C.[Date]
Option (MaxRecursion 0);

Martin Smith 14 年前

WITH Dimdate  As
(
SELECT DATEADD(DAY,-number,CAST('2009-12-31' AS DATETIME)) AS [Date]
from master.dbo.spt_values where type='p'
),
BioUser  AS
(SELECT 1 AS [UserId], 178 AS [Weight], CAST('20091012' AS DATETIME) AS DateAdded
UNION ALL
SELECT 1 AS [UserId], 175 AS [Weight], CAST('20091018' AS DATETIME)
UNION ALL
SELECT 1 AS [UserId], 172 AS [Weight], CAST('20091027' AS DATETIME)
),
NumberedT AS
(
SELECT [UserId],[Weight],DateAdded, 
       ROW_NUMBER() OVER (PARTITION BY [UserId] ORDER BY DateAdded) AS RN
FROM BioUser 
)
SELECT  
     ISNULL(T1.[UserId], T2.[UserId]) [UserId], 
     ISNULL(T1.Weight, T2.Weight) [Weight], 
     Dimdate.[Date]
 FROM NumberedT T1
FULL OUTER JOIN NumberedT T2 ON T2.RN = T1.RN+1 AND T2.[UserId]= T1.[UserId]
INNER JOIN Dimdate ON 
    (Dimdate.[Date] >= ISNULL(T1.DateAdded, T2.DateAdded) 
        AND Dimdate.[Date]< T2.DateAdded)
OR
    (T2.DateAdded IS NULL AND Dimdate.[Date]=T1.DateAdded)
ORDER BY Dimdate.[Date]

Dustin Laine 14 年前

你应该从 DimDate 表和 JOIN 这个 BioUser 关于这一点:

SELECT u.Weight, u.DateAdded
FROM DimDate d
    LEFT OUTER JOIN d.Date = u.DateAdded

NULL 对于未填充在 生物使用者 桌子。

Beth 14 年前

Select Weight, Date 
from Dimdate d left outer join BioUser b
on b.DateAdded= d.Date

如果当天没有权重值,则应该为权重返回null。

HTH公司

您需要从dimDate表中提取日期,而不是bioUser,后者可能为空

Vishal 14 年前

SELECT    
        (SELECT TOP (1) Weight
         FROM BioUser AS b
         WHERE (CAST(DateTested AS Date) <= k.Date) AND (UserId= @UserId)
         ORDER BY DateTested DESC) AS Weight, Date AS DateTested
FROM     DimDate AS k
WHERE     (Date BETWEEN @StartDate AND @EndDate)