3  

One way to do it (which also works in SQL Server 2008)

一种方法(也适用于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

Output:

输出:

| 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 |

Here is SQLFiddle demo

这是SQLFiddle演示

3  

Edit:

编辑:

Since you've tagged with sql-server-2012, you will have access to the LEAD and LAG analytic functions, which allow you to 'peek' at other rows. I've also assumed you need to 'partition by' the RiskName.

由于您已经使用sql-server-2012进行了标记，您将可以访问LEAD和LAG analytic functions，它允许您“窥视”其他行。我还假设您需要通过RiskName进行分区。

SELECT TOP 2
    RiskId, RiskName, RiskDate AS Recent_R_Date, RiskScore AS Recent_R_Score, 
    LAG(RiskDate) OVER (PARTITION BY RiskName ORDER BY RiskDate ASC) AS Second_R_Date,
    LAG(RiskScore) OVER (PARTITION BY RiskName ORDER BY RiskDate ASC) AS Second_R_Score,
    ItemID
 FROM Risk
 WHERE ItemID = 12
 ORDER BY RiskDate DESC;

Fiddle here

小提琴在这里