我想在版本列中選擇一條具有最新值的記錄。但是，如果我包含retrn_period日期類型的列，我的查詢將返回多條記錄。SELECT rdo_code, batch_no, reference_no, dln, retrn_seq_num, ftype_code, tin, branch_code, tax_type, retrn_period, Max(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, retrn_period;
3 回答
MM們
TA貢獻1886條經驗 獲得超2個贊
如果我包含 retrn_period 列...我的查詢將返回多條記錄。
通過包含該retrn_period列，您正在更改查詢的非聚合投影，因此現在您可以獲得結果集中每個不同日期的最大版本。
您想顯示最新版本的日期嗎？假設您的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;
一個更通用的解決方案適用于無法聚合的東西，比如說name，使用帶有分析函數的子查詢......
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 限制語法，這要簡單得多：
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;
慕妹3146593
TA貢獻1820條經驗 獲得超9個贊
如果你想選擇一個單一的記錄——版本號最高——那么我認為你根本不需要對任何東西進行分組。
按版本號排序行并獲取前 1 應該這樣做：
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 UPPER(status) = UPPER('POSTED')
ORDER BY version DESC
FETCH FIRST 1 ROWS ONLY;
此查詢假定您正在運行 Oracle 12c。
蕪湖不蕪
TA貢獻1796條經驗 獲得超7個贊
You can also use rownum=1
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 UPPER(status) = UPPER('POSTED')
AND rownum=1
ORDER BY version DESC;
---------------------------------------------------------------------------------------
or subquery like
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 a
WHERE
tin = '000079108'
AND ftype_code = '1702EX'
AND UPPER(status) = UPPER('POSTED')
AND a.version = (SELECT Max(b.version)
FROM rfp_returns_ref b
WHERE b.tin = a.tin
AND b.ftype_code = a.ftype_code
AND b.UPPER(status) = UPPER(a.status));
添加回答
舉報
0/150
提交
取消