oracle无法获得最后一个payment_date，payment_sum

Question

我有以下查询：

SELECT
  p.id,
  last_date_ps.pay_date last_pay_date
FROM projects p
  LEFT JOIN
  (
    SELECT
      pp.project_id,
      max(pp.pay_date) AS pay_date,
      pp.pay_sum
    FROM project_partuals pp
    WHERE pp.status IN (2, 4) AND pp.pay_sum > 0 AND pp.pay_date IS NOT NULL
    GROUP BY pp.project_id
  ) last_date_ps ON last_date_ps.project_id = p.id,
  contacts c
WHERE (p.debtor_contact_id = c.id)
ORDER BY priority_value DESC, name_f ASC;

我得到了一个错误：

Error: ORA-00979: not a GROUP BY expression

SQLState:  42000
ErrorCode: 979
Position: 216

当我删除pp.pay_sum查询时工作。我怎样才能进入左会合(.)按日期分列的pay_date和pay_sum顺序(最大日期)

Answer 1

如果希望将每个项目的pay_sum作为内部查询的结果，则需要聚合它：

    (select pp.project_id, max(pp.pay_date) as pay_date, sum(pp.pay_sum) as  pay_sum
    from project_partuals pp 
    where pp.status in (2,4) and pp.pay_sum > 0 and pp.pay_date is not null 
    group by pp.project_id ) last_date_ps 

如果只想获得每个项目的最后付款，内部查询应该是：

(select project_id, pay_date, pay_sum FROM
  (select pp.project_id, pp.pay_date, pp.pay_sum, 
   row_number() over (PARTITION  by pp.project_id order by pp.pay_date desc) rn
   from project_partuals pp  where pp.status in (2,4) and pp.pay_sum > 0 and pp.pay_date is not null 
  ) X where X.rn = 1)

Answer 2

如果使用group子句，则必须在查询中包含除常量和聚合函数中的所有其他选定列之外的所有其他选定列，因此在其中包括pp.pay_sum：

 select 
        p.id,
          last_date_ps.pay_date last_pay_date

from projects p
        left join
                    (select pp.project_id,max(pp.pay_date) as pay_date, pp.pay_sum 
                          from project_partuals pp
                          where pp.status in (2,4) and pp.pay_sum > 0 and pp.pay_date is not null 
                        group by pp.project_id, pp.pay_sum
                     ) last_date_ps on last_date_ps.project_id = p.id

      join contacts c on (p.debtor_contact_id=c.id)
order by priority_value desc, name_f asc