sql - WHERE 中不允许使用聚合函数

Question

我正在使用此查询使用 postgresql 按最新日期查找唯一记录。我遇到的错误是“WHERE 中不允许使用聚合函数”。 How to fix error “aggregate functions are not allowed in WHERE”通过此链接，我尝试使用内部选择功能。但这没有用。请帮我编辑查询。我正在使用 PgAdmin III 作为客户端。

SELECT Distinct t1.pa_serial_
    ,t1.homeownerm_name
    ,t1.districtvdc
    ,t1.date as firstrancheinspection_date
    ,t1.status
    ,t1.name_of_data_collector
    ,t1.fulcrum_id
    ,first_tranche_inspection_v2_reporting_questionnaire.date_reporting
From first_tranche_inspection_v2 t1
LEFT JOIN first_tranche_inspection_v2_reporting_questionnaire ON (t1.fulcrum_id = first_tranche_inspection_v2_reporting_questionnaire.fulcrum_parent_id)
where first_tranche_inspection_v2_reporting_questionnaire.date_reporting = (
        select Max(first_tranche_inspection_v2_reporting_questionnaire.date_reporting)
        from first_tranche_inspection_v2
        where first_tranche_inspection_v2.pa_serial_ = t1.pa_serial_
        );

Answer 1

您想加入每次检查的最新报告问卷。在 PostgreSQL 中，您可以为此使用 DISTINCT ON:

select fti.*, rq.* 
from first_tranche_inspection_v2 fti
left join
(
  select distinct on (fulcrum_parent_id) *
  from first_tranche_inspection_v2_reporting_questionnaire
  order by fulcrum_parent_id, date_reporting desc
) rq on rq.fulcrum_parent_id = fti.fulcrum_id;

或者使用标准 SQL 的 ROW_NUMBER:

select fti.*, rq.* 
from first_tranche_inspection_v2 fti
left join
(
  select 
    ftirq.*, 
    row_number() over (partition by fulcrum_parent_id order by date_reporting desc) as rn
  from first_tranche_inspection_v2_reporting_questionnaire ftirq
) rq on rq.fulcrum_parent_id = fti.fulcrum_id and rq.rn = 1;

您尝试执行的操作应如下所示:

select fti.*, rq.* 
from first_tranche_inspection_v2 fti
left join first_tranche_inspection_v2_reporting_questionnaire rq
  on rq.fulcrum_parent_id = fti.fulcrum_id
  and (rq.fulcrum_parent_id, rq.date_reporting) in
  (
    select fulcrum_parent_id, max(date_reporting)
    from first_tranche_inspection_v2_reporting_questionnaire
    group by fulcrum_parent_id
  );

这也有效，唯一的缺点是您需要阅读表格 first_tranche_inspection_v2_reporting_questionnaire 两次。