sql-server - SQL Server-选择前2行

我正在尝试编写一个查询，该查询将返回


最近的AccountDate，每个locationID的记录为0
然后是每个locationID的倒数第二个AccountDate。记录可以是1或0。
如果有两个具有相同日期的AccountDate，则根据DateAccountLoaded返回最近的AccountDate


但是我的解决方案看起来并不十分优雅。有谁有更好的方法来实现这一目标。

请看下面我的解决方案

CREATE TABLE [dbo].[TopTwoKeyed](
ID  INT IDENTITY(1,1) PRIMARY KEY(ID),
[LocationID] [int] NULL,
[AccountDate] [date] NULL,
[Record] [tinyint] NULL,
[DateAccountLoaded] [date] NULL
)

INSERT INTO [dbo].[TopTwoKeyed] (
        [LocationID],
        AccountDate,
        Record,
        DateAccountLoaded
        )

VALUES(1,'2009-10-31',0,'2011-03-23'),
(1,'2008-10-31',1,'2011-03-23'),
(1,'2008-10-31',0,'2010-03-22'),
(1,'2008-10-31',1,'2009-03-23'),
(1,'2011-10-31',1,'2010-03-22'),
(1,'2009-10-31',0,'2010-03-23'),
(2,'2011-10-31',0,'2010-03-23'),
(2,'2010-10-31',0,'2010-03-23'),
(2,'2010-10-31',1,'2010-03-23'),
(2,'2010-10-31',1,'2009-03-23'),
(3,'2010-10-31',0,'2010-03-23'),
(3,'2009-10-31',0,'2010-03-23'),
(3,'2008-10-31',1,'2010-03-23')




    -- Get the most recent Account Date per locationID which has a record type of 0
    SELECT  f.LocationID
            ,f.AccountDate
            ,f.DateAccountLoaded
    FROM    (
            SELECT  ROW_NUMBER() OVER (PARTITION BY LocationID ORDER BY AccountDate DESC,DateAccountLoaded DESC) AS RowNumber
                    ,LocationID AS LocationID
                    ,AccountDate AS AccountDate
                    ,DateAccountLoaded AS DateAccountLoaded
            FROM    [dbo].[TopTwoKeyed]
            WHERE   Record = 0
            ) f
    WHERE   f.RowNumber = 1

    UNION ALL

    SELECT  ff.LocationID
            ,ff.AccountDate
            ,ff.DateAccountLoaded
    FROM    (
            -- Get the SECOND most recent AccountDate. Can be either Record 0 or 1.
            SELECT  ROW_NUMBER() OVER (PARTITION BY LocationID ORDER BY AccountDate DESC,DateAccountLoaded DESC) AS RowNumber
                    ,LocationID AS LocationID
                    ,AccountDate AS AccountDate
                    ,DateAccountLoaded 'DateAccountLoaded'
            FROM    [dbo].[TopTwoKeyed] tt
            WHERE   EXISTS
                    (
                    -- Same query as top of UNION. Get the most recent Account Date per locationID which has a record type of 0
                    SELECT  1
                    FROM    (
                            SELECT  ROW_NUMBER() OVER (PARTITION BY LocationID ORDER BY AccountDate DESC,DateAccountLoaded DESC) AS RowNumber
                                    ,LocationID AS LocationID
                                    ,AccountDate AS AccountDate
                            FROM    [dbo].[TopTwoKeyed]
                            WHERE   Record = 0
                            ) f
                    WHERE   f.RowNumber = 1
                    AND     tt.LocationID = f.LocationID
                    AND     tt.AccountDate < f.AccountDate
                    )
            ) ff
    WHERE   ff.RowNumber = 1

-- DROP TABLE [dbo].[TopTwoKeyed]

最佳答案

您可以使用row_number子查询来查找最近的帐户日期。然后，您可以outer apply搜索下一个最近的帐户日期：

select  MostRecent.LocationID
,       MostRecent.AccountDate
,       SecondRecent.AccountDate
from    (
        select  row_number() over (partition by LocationID order by
                    AccountDate desc, DateAccountLoaded desc) as rn
        ,       *
        from    TopTwoKeyed
        where   Record = 0
        ) MostRecent
outer apply
        (
        select  top 1 *
        from    TopTwoKeyed
        where   Record in (0,1)
                and LocationID = MostRecent.LocationID
                and AccountDate < MostRecent.AccountDate
        order by
                AccountDate desc
        ,       DateAccountLoaded desc
        ) SecondRecent
where   MostRecent.rn = 1

编辑：要将行放置在彼此下方，您可能必须使用union。单个row_number无法工作，因为第二行的Record列具有不同的标准。

; with  Rec0 as
        (
        select  ROW_NUMBER() over (partition by LocationID
                    order by AccountDate desc, DateAccountLoaded desc) as rn
        ,       *
        from    TopTwoKeyed
        where   Record = 0
        )
,       Rec01 as
        (
        select  ROW_NUMBER() over (partition by LocationID
                    order by AccountDate desc, DateAccountLoaded desc) as rn
        ,       *
        from    TopTwoKeyed t1
        where   Record in (0,1)
                and not exists
                (
                select  *
                from    Rec0 t2
                where   t2.rn = 1
                        and t1.LocationID = t2.LocationID
                        and t2.AccountDate < t1.AccountDate
                )
        )
select  *
from    Rec0
where   rn = 1
union all
select  *
from    Rec01
where   rn = 1