我正在尝试获取最新记录并找到非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
所需结果
为每个
dcrp
获取最新的sig_id
:| ID | DD | SIG_ID | DCRP |
----------------------------------------
| 5 | 2011-03-01 | 1 |专家二|
| 12 | 2011-03-01 | 2 |高级
| 15 | 2010-12-01 | 3 | (空)|
SQL Fiddle
最佳答案
您可以使用以下内容:
;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。