如果 select 语句包含 DATE 类型的列，则聚合 sql 查询不起作用

Question

我想在版本列中选择一条具有最新值的记录。但是，如果我包含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;

Answer 1

如果我包含 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;

Answer 2

如果你想选择一个单一的记录——版本号最高——那么我认为你根本不需要对任何东西进行分组。

按版本号排序行并获取前 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。

Answer 3

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));