sql - 获取最近的非空记录

Question

我正在尝试获取最新记录并找到非NULL 匹配项。问题是我的子查询返回多个结果。

数据集

| ID |         DD | SIG_ID |      DCRP |----------------------------------------|  1 | 2010-06-01 |      1 |    Expert ||  2 | 2010-09-01 |      1 |    Expert ||  3 | 2010-12-01 |      1 |    Expert ||  4 | 2010-12-01 |      1 | Expert II ||  5 | 2011-03-01 |      1 | Expert II ||  6 | 2011-06-01 |      1 |    (null) ||  7 | 2010-06-01 |      2 |    Senior ||  8 | 2010-09-01 |      2 |    Senior ||  9 | 2010-09-01 |      2 |    Senior || 10 | 2010-12-01 |      2 | Senior II || 11 | 2011-03-01 |      2 |    (null) || 12 | 2011-03-01 |      2 |    Senior || 13 | 2010-06-01 |      3 |    (null) || 14 | 2010-09-01 |      3 |    (null) || 15 | 2010-12-01 |      3 |    (null) |

Query

SELECT a.sig_id, a.id, 
  CASE
    WHEN b.dcrp IS NULL
      THEN
        (SELECT dcrp
         FROM tbl
         WHERE sig_id = a.sig_id
           AND id < a.id
           AND dcrp IS NOT NULL)
      ELSE b.dcrp
  END AS dcrp
FROM
  (SELECT sig_id, MAX(id) id
  FROM tbl
  GROUP BY sig_id) a
LEFT JOIN
  (SELECT id, dcrp
   FROM tbl
   WHERE dcrp IS NOT NULL) b ON b.id = a.id

期望的结果

获取每个 sig_id 的最新 dcrp:

| ID |         DD | SIG_ID |      DCRP |----------------------------------------|  5 | 2011-03-01 |      1 | Expert II || 12 | 2011-03-01 |      2 |    Senior || 15 | 2010-12-01 |      3 |    (null) |

SQL Fiddle

Answer 1

您可以使用以下内容:

;WITH CTE AS
(
    SELECT *, ROW_NUMBER() OVER(PARTITION BY SIG_ID 
                                ORDER BY CASE WHEN DCRP IS NOT NULL THEN 0 ELSE 1 END,
                                DD DESC) RN
    FROM tbl
)
SELECT *
FROM CTE 
WHERE RN = 1

还有fiddle .