SQL Server：每组第二个最大值

Ramu 发表于 Dev

拉木

我有一张数据如下的表

CREATE TABLE Tbl
(
  ID VARCHAR(20)
  , Desc1 VARCHAR(30)
  , BALANCE_DATE DATE
);

INSERT INTO Tbl (ID, Desc1, BALANCE_DATE)
VALUES ('ID1', 'Desc 1', '10/18/2015')
  , ('ID1', 'Desc 2', '10/19/2015')
  , ('ID1', 'Desc 3', '10/22/2015')
  , ('ID1', 'Desc 4', GETDATE())
  , ('ID2', 'Desc 1', '9/18/2015')
  , ('ID2', 'Desc 2', '9/1/2015')
  , ('ID2', 'Desc 3', '9/28/2015')
  , ('ID2', 'Desc 4', GETDATE());

╔═════╦════════╦════════════════╗
║ ID  ║ Desc1  ║ BALANCE_DATE   ║
╠═════╬════════╬════════════════╣
║ ID1 ║ Desc 1 ║ 10/18/2015     ║
║ ID1 ║ Desc 2 ║ 10/19/2015     ║
║ ID1 ║ Desc 3 ║ 10/22/2015     ║
║ ID1 ║ Desc 4 ║ 11/21/2015     ║
║ ID2 ║ Desc 1 ║ 09/18/2015     ║
║ ID2 ║ Desc 2 ║ 09/01/2015     ║
║ ID2 ║ Desc 3 ║ 09/28/2015     ║
║ ID2 ║ Desc 4 ║ 11/21/2015     ║
╚═════╩════════╩════════════════╝

我需要获取除当前日期以外的最新日期的行。

预期产量：

╔═════╦════════╦════════════╗
║ ID1 ║ Desc 3 ║ 10/22/2015 ║
║ ID2 ║ Desc 3 ║ 9/28/2015  ║
╚═════╩════════╩════════════╝

如何查询呢？

埃瓦尔达斯（Evaldas Buinauskas）

尽管Joe的答案行之有效，但我强烈建议在这种情况下使用排名函数，您不必两次连接表，这对他们来说是一个完美的用例。这将是以下查询：

DECLARE @Tbl TABLE
(
  ID VARCHAR(20)
  , Desc1 VARCHAR(30)
  , BALANCE_DATE DATE
);


INSERT INTO @Tbl (ID, Desc1, BALANCE_DATE)
VALUES ('ID1', 'Desc 1', '10/18/2015')
  , ('ID1', 'Desc 2', '10/19/2015')
  , ('ID1', 'Desc 3', '10/22/2015')
  , ('ID1', 'Desc 4', GETDATE())
  , ('ID2', 'Desc 1', '9/18/2015')
  , ('ID2', 'Desc 2', '9/1/2015')
  , ('ID2', 'Desc 3', '9/28/2015')
  , ('ID2', 'Desc 4', GETDATE());
  
;WITH CTE(ID, Desc1, BALANCE_DATE, RN)
AS (
  SELECT ID
    , Desc1
    , BALANCE_DATE
    , DENSE_RANK() OVER(PARTITION BY Id ORDER BY BALANCE_DATE DESC)
  FROM @Tbl
 )
SELECT T.ID
  , T.Desc1
  , T.BALANCE_DATE
FROM CTE AS T
WHERE T.RN = 2;

＃＃＃结果：

╔═════╦════════╦═════════════════════╗
║ ID  ║ Desc1  ║    BALANCE_DATE     ║
╠═════╬════════╬═════════════════════╣
║ ID1 ║ Desc 3 ║ 2015-10-22 00:00:00 ║
║ ID2 ║ Desc 3 ║ 2015-09-28 00:00:00 ║
╚═════╩════════╩═════════════════════╝

此查询应始终为每个具有第二高日期的ID选择行。

如果要在线测试，请参阅data.stackexchange.com上的构造查询。

＃＃＃更新

INSERT INTO @Tbl (ID, Desc1, BALANCE_DATE)
VALUES ('ID1', 'Desc 1', '10/18/2015')
  , ('ID1', 'Desc 2', '10/19/2015')
  , ('ID1', 'Desc 3', '10/22/2015')
  , ('ID1', 'Desc 4', GETDATE())
  , ('ID2', 'Desc 1', '9/18/2015')
  , ('ID2', 'Desc 2', '9/1/2015')
  , ('ID2', 'Desc 3', '9/28/2015')
  , ('ID2', 'Desc 4', GETDATE())
  , ('ID3', 'Desc 1', '10/18/2015')
  , ('ID3', 'Desc 2', '10/15/2015');

;WITH CTE(ID, Desc1, BALANCE_DATE, RN)
AS (
  SELECT ID
    , Desc1
    , BALANCE_DATE
    , DENSE_RANK() OVER(PARTITION BY Id ORDER BY BALANCE_DATE DESC)
  FROM @Tbl
  WHERE BALANCE_DATE < CONVERT(DATE, GETDATE())
 )
SELECT T.ID
  , T.Desc1
  , T.BALANCE_DATE
FROM CTE AS T
WHERE T.RN = 1;

＃＃＃结果：

╔═════╦════════╦══════════════╗
║ ID  ║ Desc1  ║ BALANCE_DATE ║
╠═════╬════════╬══════════════╣
║ ID1 ║ Desc 3 ║ 2015-10-22   ║
║ ID2 ║ Desc 3 ║ 2015-09-28   ║
║ ID3 ║ Desc 1 ║ 2015-10-18   ║
╚═════╩════════╩══════════════╝

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。