sql - 如何在 SQL Server 中获取最近和第二个最近的值

Question

我有一个包含与风险相关的数据的表，我必须从风险表中获取最新和第二秒的最新数据

表中数据如下:

RiskID        RiskName     RiskScore       RiskDate    ItemID      
1             ABC            10          23/10/2013       12            
1             ABC            8           20/12/2013       12 
1             ABC            5           15/01/2014       12
2             BC             9           19/09/2013       12
2             BC             10          17/12/2013       12
2             BC             9           12/01/2014       12

我正在寻找如下结果，其中 ItemID 应为 12

R_ID   R_Name  Recent_R_Date  Recent_R_Score  Second_R_Date   Second_R_Date  ItemID
1       ABC       15/01/2014      5              20/12/2013       8           12
2       BC        12/01/2014      9             17/12/2013        10          12

Answer 1

一种方法(也适用于 SQL Server 2008)

SELECT RiskID, RiskName,
       MAX(CASE WHEN rnum = 1 THEN RiskDate END) RecentDate,
       MAX(CASE WHEN rnum = 1 THEN RiskScore END) RecentScore,
       MAX(CASE WHEN rnum = 2 THEN RiskDate END) SecondDate,
       MAX(CASE WHEN rnum = 2 THEN RiskScore END) SecondScore,
       ItemID
  FROM
(
  SELECT *, ROW_NUMBER() OVER (PARTITION BY RiskID ORDER BY RiskDate DESC) rnum
    FROM Risk
   WHERE ItemID = 12
) q
 WHERE rnum <= 2
 GROUP BY ItemID, RiskID, RiskName
 ORDER BY RiskID

输出:

| RISKID | RISKNAME | RECENTDATE | RECENTSCORE | SECONDDATE | SECONDSCORE | ITEMID ||--------|----------|------------|-------------|------------|-------------|--------||      1 |      ABC | 2014-01-15 |           5 | 2013-12-20 |           8 |     12 ||      2 |       BC | 2014-01-12 |           9 | 2013-12-17 |          10 |     12 |

这是 SQLFiddle 演示