SQL Server:在单个查询中组合最大结果和第二大结果

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

我不明白的是，如何组合这两个语句来生成一个查询，列出所有租约的第二个最近的仪表读数日期和最近的仪表读数日期。据我所知，我需要在这种情况下使用交叉应用，但无法使其工作。谢谢!

Answer 1

如果希望两个日期都在同一行中，可以使用ROW_NUMBER()函数。您不需要将表连接到自身，只需像这样使用group by：

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()将产生意外的结果。

Answer 2

如果我没理解错的话，我想你需要得到每个LeaseID的最新的2个读数。为此，这；

SELECT LeaseID, MeterReadingDate
FROM (
    SELECT LeaseID, MeterReadingDate, 
           Rank() Over (Partition by LeaseID Order by MeterReadingDate desc) rk
    FROM   dbo.UtilityBills
) A
WHERE rk <3
ORDER BY MeterReadingDate desc

Answer 3

尝尝这个

;WITh CTE AS
(
   SELECT LEASEID, MeterReadingDate, ROW_NUMBER() OVER 
                       (PARTITION BY LEASEID 
                        ORDER BY MeterReadingDate DESC) RN
   FROM   dbo.UtilityBills
)
SELECT x1.LEASEID, x1.MeterReadingDate CurrentDate, 
x2.MeterReadingDate PreviousDate
FROM CTE x1
LEFT OUTER JOIN CTE X2 ON x1.leaseid = x2.leaseid AND x1.rn + 1 = x2.rn
WHERE X1.rn = 1