我有以下查询:
SELECT P.ID,
(
SELECT F.VALUE_ID FROM FORM F
INNER JOIN F.PERSON_ID = P.ID
) AS LATEST_FORM
FROM FORM F1 INNER JOIN PERSON P
ON P.ID = F1.PERSON_ID
WHERE F1.NO_PCP_IND IS NOT NULL
ORDER BY P.ID
每个人可以有多个表格,而我只是想获取他们提交的最新表格。我尝试在子查询中使用ROWNUM,但我理解为什么它不起作用,但不确定如何使它通过Oracle生成正确的结果集。
这是查询:
SELECT P.ID,
(SELECT F.VALUE_ID
FROM FORM F INNER JOIN
F.PERSON_ID = P.ID
) AS LATEST_FORM
FROM FORM F1 INNER JOIN
PERSON P
ON P.ID = F1.PERSON_ID
WHERE F1.NO_PCP_IND IS NOT NULL
ORDER BY P.ID;
除了语法上不正确之外,如果可行,这还将返回每人和用户一行。我认为您的原始查询应该更像:
SELECT P.ID,
(SELECT F.VALUE_ID
FROM FORM F
WHERE F1.NO_PCP_IND IS NOT NULL AND F.PERSON_ID = P.ID AND
ROWNUM = 1
) AS LATEST_FORM
FROM PERSON P
ORDER BY P.ID;
这不能解决您的问题,因为您想保留最后一个值。为此,请使用以下keep
功能:
SELECT P.ID,
(SELECT MAX(F.VALUE_ID) KEEP (DENSE_RANK FIRST ORDER BY XXX DESC)
FROM FORM F
WHERE F1.NO_PCP_IND IS NOT NULL AND F.PERSON_ID = P.ID
) AS LATEST_FORM
FROM PERSON P
ORDER BY P.ID;
在这种情况下XXX
,该列的名称定义了每个人的表单顺序。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句