最大日期的SQL查询

Question

我问了a related question和我得到了一些非常好的答案，但我试图在不做临时表的情况下进行查询。我相信这是可以做到的，但我想不出怎么做。

我有两个表，我想得到每个条目的最新状态，其中状态日期小于或等于输入日期。

条目表(条目)：

  UserID   | EntryDate  
 ----------------------
    1      |  5/4/2010  
    1      |  4/4/2010  
    1      |  3/4/2010  
    1      |  2/4/2010  
    2      |  5/4/2010
    2      |  4/4/2010

条目状态表(状态)：

  UserID   | StatusDate  | Detail
  -------------------------------
    1      |  5/28/2010  |  D-1
    1      |  4/24/2010  |  D-2
    1      |  4/5/2010   |  D-3
    1      |  2/28/2010  |  D-4

预期输出：

UserID | EntryDate | Detail
---------------------------
  1    | 5/4/2010  |  D-2
  1    | 4/4/2010  |  D-4
  1    | 3/4/2010  |  D-4
  1    | 2/4/2010  | <NULL>

在我的previous question中，您可以看到我的努力和相关的答案，但为了简化问题，我缩小了范围。

我试着做这样的事情(但我知道我肯定要离开了)：

         SELECT E.EntryDate, 
                S.Detail, 
                MAX(S.StatusDate) AS MaxStatusDate
           FROM Entry AS E 
LEFT OUTER JOIN Status AS S ON E.EntryDate >= S.StatusDate

Answer 1

我想你想要的是

SELECT E.UserID
    , E.EntryDate
    , (SELECT TOP 1 Detail
       FROM Status AS S
       WHERE S.UserID = E.UserID
       AND S.StatusDate <= E.EntryDate
       ORDER BY S.StatusDate DESC)
FROM Entry AS E

如果您的数据库不支持TOP，或者出于性能原因，您希望避免使用ORDER BY，您可以尝试这样的方法：

SELECT E.UserID
    , E.EntryDate
    , (SELECT S1.Detail
       FROM Status AS S1
       WHERE S1.UserID = E.UserID
       AND S1.StatusDate = (SELECT MAX(S2.StatusDate)
                            FROM Status AS S2
                            WHERE S2.UserID = E.UserID
                            AND S2.StatusDate <= E.EntryDate))
FROM Entry AS E

Answer 2

我不认为有一种通用的方法，但在某些SQL方言中，您可以在子查询中使用TOP或LIMIT来根据另一列中的排序(ORDER )从第一个匹配记录中的列中获取值。

比如说..。

SELECT
  Entry.*,
  ( SELECT TOP 1 
      Status.Detail
    FROM Status
    WHERE
      Entry.UserID = Status.UserID AND
      Entry.EntryDate >= Status.StatusDate
    ORDER BY
      Entry.EntryDate
  ) As StatusDetail

Answer 3

对于支持它的数据库，采用row_number方式：

SELECT  *
FROM    (
        SELECT  ROW_NUMBER() OVER (
                    PARTITION BY e.UserId, e.EntryDate
                    ORDER BY s.StatusDate desc) as rn
        ,       *
        FROM    Entry e
        INNER JOIN    
                Status s
        ON      s.UserID = e.UserID
                AND s.StatusDate <= e.EntryDate
        ) as SubQueryAlias
where   rn = 1