SQL 服务器 : combine maximum result and second largest results in single query

Question

我认为这应该很容易，但我想不通。

这里是一些背景信息:

我有两个表，名为 Leases 和 UtilityBills。它们通过 UtilityBills 表中称为 LeaseID 的外键连接，该外键引用 Leases 表中的主键(也称为 LeaseID)。

所以这很简单——我正在记录许多租约中每一个的每月电表读数。

在 UtilityBills 表中，我有一个名为 MeterReadingDate 的字段，用于存储每次抄表的日期。

这是我的问题:

我如何创建一个查询，为每个租约提供最近的抄表日期和上一次抄表的日期？

使用以下 sql 语句，我能够很容易地获取每个租约的最新仪表读数:

SELECT LeaseID, MAX(MeterReadingDate) AS MostRecentMeterReadingDate
FROM   dbo.UtilityBills
GROUP BY LeaseID

我还可以使用此 sql 语句获取任何给定租约的先前抄表(例如，这为我提供了 LeaseID=228 的租约的先前抄表):

SELECT TOP 1 MeterReadingDate, LeaseID
FROM   (SELECT TOP 2 MeterReadingDate, LeaseID
                FROM    dbo.UtilityBills
                WHERE (LeaseID = 228)
                ORDER BY MeterReadingDate DESC) DERIVEDTBL
ORDER BY MeterReadingDate

我想不通的是，如何将这两个语句结合起来生成一个查询，列出所有租约的倒数第二个和最近的抄表日期。据我所知，在这种情况下我需要使用 CROSS APPLY，但无法正常工作。谢谢!

Answer 1

如果您希望两个日期在同一行中，您可以使用 ROW_NUMBER() 函数。您不需要将表格连接到自身，只需像这样使用分组方式:

IF OBJECT_ID('dbo.UtilityBills') IS NOT NULL DROP TABLE dbo.UtilityBills;

CREATE TABLE dbo.UtilityBills(
Id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
LeaseId INT,
MeterReadingDate DATE
);

INSERT INTO dbo.UtilityBills(LeaseId,MeterReadingDate)VALUES 
  (1,'2012-01-01'),
  (1,'2012-02-01'),
  (1,'2012-03-01'),
  (1,'2012-04-01'),
  (2,'2012-01-02'),
  (2,'2012-03-02'),
  (2,'2012-05-02'),
  (3,'2012-08-03'),
  (3,'2012-10-03'),
  (4,'2012-05-04');

SELECT LeaseId,
MAX(CASE WHEN rn = 1 THEN MeterReadingDate END) MostRecentReadingDate,
MAX(CASE WHEN rn = 2 THEN MeterReadingDate END) PreviousReadingDate
FROM(
SELECT  LeaseID,
        MeterReadingDate,
        ROW_NUMBER() OVER ( PARTITION BY LeaseId ORDER BY MeterReadingDate DESC ) rn
FROM    dbo.UtilityBills
)AS U
WHERE rn <=2
GROUP BY LeaseId;

这里还有一些其他答案建议使用 RANK() 函数而不是 ROW_NUMBER()。但是，如果最后两次读数发生在同一天，RANK() 将产生意想不到的结果。