如果我包含 retrn_period 列...我的查詢將返回多條記錄。
通過(guò)包含該retrn_period列，您正在更改查詢的非聚合投影，因此現(xiàn)在您可以獲得結(jié)果集中每個(gè)不同日期的最大版本。
您想顯示最新版本的日期嗎？假設(shè)您的retrn_period增加與version此一起工作：
SELECT rdo_code, batch_no, reference_no, dln, retrn_seq_num, ftype_code, tin,
branch_code, tax_type
, max(retrn_period) as retrn_period
, max(version) as version
FROM rfp_returns_ref
WHERE tin = '000079108'
AND ftype_code = '1702EX'
AND Upper(status) = Upper('POSTED')
group by rdo_code, batch_no,reference_no, dln,
retrn_seq_num, ftype_code, tin, branch_code, tax_type;
一個(gè)更通用的解決方案適用于無(wú)法聚合的東西，比如說(shuō)name，使用帶有分析函數(shù)的子查詢......
SELECT sq.rdo_code, sq.batch_no, sq.reference_no, sq.dln, sq.retrn_seq_num, sq.ftype_code, sq.tin,
sq.branch_code, sq.tax_type
, sq.retrn_period
, sq.name
, sq.version
from (
SELECT rdo_code, batch_no, reference_no, dln, retrn_seq_num, ftype_code, tin,
branch_code, tax_type
, retrn_period
, version
, name
, rank() over (partition by rdo_code, batch_no,reference_no, dln,
retrn_seq_num, ftype_code, tin, branch_code, tax_type
order by version desc ) as rn
FROM rfp_returns_ref
WHERE tin = '000079108'
AND ftype_code = '1702EX'
AND Upper(status) = Upper('POSTED')
) sq
where sq.rn = 1 ;
如果您使用的是 Oracle 12c，則可以使用 fetch 限制語(yǔ)法，這要簡(jiǎn)單得多：
SELECT rdo_code, batch_no, reference_no, dln, retrn_seq_num, ftype_code, tin,
branch_code, tax_type
, retrn_period
, version
FROM rfp_returns_ref
WHERE tin = '000079108'
AND ftype_code = '1702EX'
AND order by version desc
fetch first 1 row only;
