如何在SQL Server中获取最新和最近的值

Dev Developer 发表于 Dev

开发人员

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

表中数据如下：

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

术语

一种方法（在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

输出：

| 风险| 风险名称| 近期| 最近评分| SECONDDATE | SECONDSCORE | ITEMID |
| -------- | ---------- | ------------ | ------------- |- ---------- | ------------- || -------- |
| 1 | ABC | 2014-01-15 | 5 | 2013-12-20 | 8 | 12 |
| 2 | 卑诗省| 2014-01-12 | 9 | 2013-12-17 | 10 | 12 |