Oracle数据库：如何仅获取Blob分组中的最大值对应行？

你可以通过添加过滤条件，仅保留Max_Value等于对应OIN最大值Max_Sum的行。修改后的SQL语句如下：

WITH q1 (key, fileformat, originatingchannelidx, submitterididx, "InitParty", "OIN", "Max_Value")
AS (
    SELECT 
        PINT.PAYMENTINTERCHANGEKEY, 
        pint.fileformat,  
        pint.originatingchannelidx, 
        pint.submitterididx, 
        XMLQuery('//*:GrpHdr/*:InitgPty/*:Nm/text()'
            passing XMLtype(pint.transportdata, nls_charset_id('AL32UTF8')) 
            returning content).getstringval() "InitParty",
        XMLQuery('//*:GrpHdr/*:InitgPty/*:Id/*:OrgId/*:Othr/*:Id/text()'
            passing XMLtype(transportdata, nls_charset_id('AL32UTF8')) 
            returning content).getstringval() "OIN",
        to_number(XMLQuery('//*:GrpHdr/*:CtrlSum/text()' 
            passing XMLtype(pint.transportdata, nls_charset_id('AL32UTF8')) 
            returning content).getstringval()) "max_value"
    FROM bph_owner.paymentinterchange pint
    WHERE
        pint.TRANSPORTTIME >= to_date('2022-08-14', 'yyyy-mm-dd') 
        AND pint.fileformat = 'pain.001.001.03'
),
q2 AS (
    SELECT 
        Q1.fileformat, 
        q1.originatingchannelidx, 
        q1.submitterididx, 
        q1."InitParty", 
        q1."OIN", 
        q1."Max_Value",
        MAX("Max_Value") OVER (PARTITION BY q1."OIN") as Max_Sum
    FROM q1
)
SELECT *
FROM q2
WHERE "Max_Value" = "Max_Sum"
ORDER BY "InitParty", "OIN", "Max_Value";

修改说明 #

1. 新增q2公共表表达式，专门计算每个OIN对应的全局最大值Max_Sum
2. 在最终查询中添加WHERE "Max_Value" = "Max_Sum"条件，直接过滤掉非最大值的行
3. 移除原外层查询中多余的GROUP BY（原q1已对应唯一支付记录，该GROUP BY无聚合作用）

执行后，你将只看到每个OIN下Max_Value等于该OIN最大值的行，比如OIN 610570仅会保留Max_Value=315108.52的记录。

内容的提问来源于stack exchange，提问作者Peter warren