如何为每个唯一查询条目显示最新检查记录

要实现每个central_enquiry.enquiry_number仅显示对应最新actual_inspection.logged_time的检查记录，以下结合你的SQL给出两种实用实现方式：

方法一：使用窗口函数（推荐，简洁高效） #

利用ROW_NUMBER()窗口函数，按enquiry_number分组，对logged_time降序排序，只保留排序后行号为1的记录（即最新的那条）。

修改后的完整SQL：

SELECT
   enquiry_subject.subject_name,
   central_enquiry.enquiry_number,
   central_site.site_name,
   CAST((SELECT parameter_value.param_value_name 
         FROM parameter_value
         JOIN parameter_type ON parameter_value.param_type_code = parameter_type.param_type_code
         WHERE parameter_type.param_type_code = 'S815' 
           AND parameter_value.param_value_code = enquiry_attribute.param_value_code) as VARCHAR (30)) as Promoter,
   CAST((SELECT parameter_value.param_value_name 
         FROM parameter_value
         JOIN parameter_type ON parameter_value.param_type_code = parameter_type.param_type_code
         WHERE parameter_type.param_type_code = 'S818' 
           AND parameter_value.param_value_code = enquiry_attribute_5.param_value_code) as VARCHAR (30)) as severity,
   enquiry_status.enq_status_name,
   central_enquiry.log_text,
   sw_header.works_ref,
   sw_notice_header.notice_issue_time,
   sw_notice_header.work_start_date,
   sw_notice_header.work_est_end_date,
   sw_notice_header.work_comp_date,
   sw_notice_header.notice_type_code,
   notice_type.notice_type_name,
   sw_notice_header.works_desc,
   sw_notice_header.works_location,
   sw_works_status.works_status_name,
   actual_inspection.logged_time,
   actual_inspection.logged_by,
   actual_inspection.insp_number,
   actual_inspection.insp_outcome_code,
   actual_inspection.insp_notes,
   CASE
      WHEN ROUND((program_insp.insp_to_date - SYSDATE),2) >=60 AND ROUND((program_insp.insp_to_date - SYSDATE),2) <= 10000  THEN 'Over 60 Days'
      WHEN ROUND((program_insp.insp_to_date - SYSDATE),2) >=30 AND ROUND((program_insp.insp_to_date - SYSDATE),2) <= 60  THEN '30 - 60 Days'
      WHEN ROUND((program_insp.insp_to_date - SYSDATE),2) >=15 AND ROUND((program_insp.insp_to_date - SYSDATE),2) <= 29.99  THEN '15 - 30 Days'
      WHEN ROUND((program_insp.insp_to_date - SYSDATE),2) >=5 AND ROUND((program_insp.insp_to_date - SYSDATE),2) <= 14.99  THEN '5 - 15 Days'
      WHEN ROUND((program_insp.insp_to_date - SYSDATE),2) >=2 AND ROUND((program_insp.insp_to_date - SYSDATE),2) <= 4.99  THEN '2 - 5 Days'
      WHEN ROUND((program_insp.insp_to_date - SYSDATE),2) >=0 AND ROUND((program_insp.insp_to_date - SYSDATE),2) <= 1.99  THEN 'Today | Tomorrow'
      ELSE 'Overdue' 
   END as this_week,
   program_insp.insp_to_date,
   program_insp.insp_from_date
FROM
   enquiry_subject
INNER JOIN type_of_service ON type_of_service.service_code = enquiry_subject.service_code
INNER JOIN central_enquiry ON enquiry_subject.service_code = central_enquiry.service_code 
                          AND enquiry_subject.subject_code = central_enquiry.subject_code
INNER JOIN enquiry_class ON central_enquiry.enq_class_code = enquiry_class.enq_class_code
INNER JOIN enquiry_status ON enquiry_status.enq_status_code = central_enquiry.enq_status_code
INNER JOIN enquiry_status_log ON enquiry_status_log.enquiry_number = central_enquiry.enquiry_number 
                              AND enquiry_status_log.enquiry_log_number = central_enquiry.enquiry_log_number
INNER JOIN enquiry_attribute ON central_enquiry.enquiry_number = enquiry_attribute.enquiry_number 
                             AND enquiry_attribute.param_type_code = 'S815'
INNER JOIN enquiry_attribute enquiry_attribute_2 ON central_enquiry.enquiry_number = enquiry_attribute_2.enquiry_number 
                                                AND enquiry_attribute_2.param_type_code = 'S816'
INNER JOIN enquiry_attribute enquiry_attribute_3 ON central_enquiry.enquiry_number = enquiry_attribute_3.enquiry_number 
                                                AND enquiry_attribute_3.param_type_code = 'S817'
INNER JOIN enquiry_attribute enquiry_attribute_4 ON central_enquiry.enquiry_number = enquiry_attribute_4.enquiry_number 
                                                AND enquiry_attribute_4.param_type_code = 'S814'
INNER JOIN enquiry_attribute enquiry_attribute_5 ON central_enquiry.enquiry_number = enquiry_attribute_5.enquiry_number 
                                                AND enquiry_attribute_5.param_type_code = 'S818'
INNER JOIN sw_header ON sw_header.work_header_no = actual_inspection.work_header_no
INNER JOIN (
    SELECT 
        ai.*,
        ROW_NUMBER() OVER (PARTITION BY ce.enquiry_number ORDER BY ai.logged_time DESC) AS rn
    FROM actual_inspection ai
    JOIN central_enquiry ce ON ai.work_header_no = ce.work_header_no
) actual_inspection ON actual_inspection.rn = 1
LEFT OUTER JOIN sw_notice_header ON sw_header.work_version_no = sw_notice_header.work_version_no 
                                AND sw_header.work_header_no = sw_notice_header.work_header_no
LEFT OUTER JOIN sw_site ON sw_site.work_header_no = sw_notice_header.work_header_no 
                       AND sw_site.site_number = sw_notice_header.primary_site_no
LEFT OUTER JOIN sw_notice_site ON sw_site.work_header_no = sw_notice_site.work_header_no 
                              AND sw_site.work_version_no = sw_notice_site.work_version_no 
                              AND sw_site.site_number = sw_notice_site.site_number
LEFT OUTER JOIN central_site ON sw_notice_site.site_code = central_site.site_code
LEFT OUTER JOIN locality ON central_site.locality_id = locality.locality_id
LEFT OUTER JOIN town ON locality.town_id = town.town_id
LEFT OUTER JOIN street_work_type ON sw_notice_header.street_work_code = street_work_type.street_work_code
LEFT OUTER JOIN area ON central_site.area_code = area.area_code
LEFT OUTER JOIN sw_agreement ON sw_agreement.work_version_no = sw_notice_header.work_version_no 
                            AND sw_agreement.work_header_no = sw_notice_header.work_header_no
LEFT OUTER JOIN organisation ON organisation.organise_code = sw_header.organise_code
LEFT OUTER JOIN organise_group ON organise_group.org_group_code = organisation.org_group_code
LEFT OUTER JOIN notice_type ON notice_type.notice_type_code = sw_notice_header.notice_type_code
LEFT OUTER JOIN sw_works_status ON sw_works_status.works_status_code = sw_notice_header.works_status_code
LEFT OUTER JOIN traffic_manage ON traffic_manage.traffic_code = sw_notice_site.traffic_code
LEFT OUTER JOIN sw_excavation ON sw_excavation.excavation_code = sw_notice_header.excavation_code
LEFT OUTER JOIN site_category ON site_category.category_code = central_site.category_code
LEFT OUTER JOIN program_insp ON program_insp.work_header_no = sw_header.work_header_no
WHERE
   sw_header.works_ref IN ('1303465')
   AND type_of_service.service_code = 'SW'
   AND enquiry_subject.subject_code = '06'
   AND enquiry_status.enq_status_code NOT IN ('50')

关键修改点： #

1. 将原SQL中逗号连接表的方式改为显式INNER JOIN，逻辑更清晰，避免隐式笛卡尔积风险。
2. 对actual_inspection表做子查询，用ROW_NUMBER()为每个enquiry_number的检查记录按时间降序排号，只保留行号为1的最新记录。
3. 优化了原嵌套查询中的表连接，用显式JOIN替代逗号连接，符合现代SQL规范。

方法二：子查询获取最新时间后关联 #

先查询每个enquiry_number对应的最大logged_time，再关联原表获取完整记录：

SELECT
   enquiry_subject.subject_name,
   central_enquiry.enquiry_number,
   central_site.site_name,
   CAST((SELECT parameter_value.param_value_name 
         FROM parameter_value
         JOIN parameter_type ON parameter_value.param_type_code = parameter_type.param_type_code
         WHERE parameter_type.param_type_code = 'S815' 
           AND parameter_value.param_value_code = enquiry_attribute.param_value_code) as VARCHAR (30)) as Promoter,
   CAST((SELECT parameter_value.param_value_name 
         FROM parameter_value
         JOIN parameter_type ON parameter_value.param_type_code = parameter_type.param_type_code
         WHERE parameter_type.param_type_code = 'S818' 
           AND parameter_value.param_value_code = enquiry_attribute_5.param_value_code) as VARCHAR (30)) as severity,
   enquiry_status.enq_status_name,
   central_enquiry.log_text,
   sw_header.works_ref,
   sw_notice_header.notice_issue_time,
   sw_notice_header.work_start_date,
   sw_notice_header.work_est_end_date,
   sw_notice_header.work_comp_date,
   sw_notice_header.notice_type_code,
   notice_type.notice_type_name,
   sw_notice_header.works_desc,
   sw_notice_header.works_location,
   sw_works_status.works_status_name,
   actual_inspection.logged_time,
   actual_inspection.logged_by,
   actual_inspection.insp_number,
   actual_inspection.insp_outcome_code,
   actual_inspection.insp_notes,
   CASE
      WHEN ROUND((program_insp.insp_to_date - SYSDATE),2) >=60 AND ROUND((program_insp.insp_to_date - SYSDATE),2) <= 10000  THEN 'Over 60 Days'
      WHEN ROUND((program_insp.insp_to_date - SYSDATE),2) >=30 AND ROUND((program_insp.insp_to_date - SYSDATE),2) <= 60  THEN '30 - 60 Days'
      WHEN ROUND((program_insp.insp_to_date - SYSDATE),2) >=15 AND ROUND((program_insp.insp_to_date - SYSDATE),2) <= 29.99  THEN '15 - 30 Days'
      WHEN ROUND((program_insp.insp_to_date - SYSDATE),2) >=5 AND ROUND((program_insp.insp_to_date - SYSDATE),2) <= 14.99  THEN '5 - 15 Days'
      WHEN ROUND((program_insp.insp_to_date - SYSDATE),2) >=2 AND ROUND((program_insp.insp_to_date - SYSDATE),2) <= 4.99  THEN '2 - 5 Days'
      WHEN ROUND((program_insp.insp_to_date - SYSDATE),2) >=0 AND ROUND((program_insp.insp_to_date - SYSDATE),2) <= 1.99  THEN 'Today | Tomorrow'
      ELSE 'Overdue' 
   END as this_week,
   program_insp.insp_to_date,
   program_insp.insp_from_date
FROM
   enquiry_subject
INNER JOIN type_of_service ON type_of_service.service_code = enquiry_subject.service_code
INNER JOIN central_enquiry ON enquiry_subject.service_code = central_enquiry.service_code 
                          AND enquiry_subject.subject_code = central_enquiry.subject_code
INNER JOIN enquiry_class ON central_enquiry.enq_class_code = enquiry_class.enq_class_code
INNER JOIN enquiry_status ON enquiry_status.enq_status_code = central_enquiry.enq_status_code
INNER JOIN enquiry_status_log ON enquiry_status_log.enquiry_number = central_enquiry.enquiry_number 
                              AND enquiry_status_log.enquiry_log_number = central_enquiry.enquiry_log_number
INNER JOIN enquiry_attribute ON central_enquiry.enquiry_number = enquiry_attribute.enquiry_number 
                             AND enquiry_attribute.param_type_code = 'S815'
INNER JOIN enquiry_attribute enquiry_attribute_2 ON central_enquiry.enquiry_number = enquiry_attribute_2.enquiry_number 
                                                AND enquiry_attribute_2.param_type_code = 'S816'
INNER JOIN enquiry_attribute enquiry_attribute_3 ON central_enquiry.enquiry_number = enquiry_attribute_3.enquiry_number 
                                                AND enquiry_attribute_3.param_type_code = 'S817'
INNER JOIN enquiry_attribute enquiry_attribute_4 ON central_enquiry.enquiry_number = enquiry_attribute_4.enquiry_number 
                                                AND enquiry_attribute_4.param_type_code = 'S814'
INNER JOIN enquiry_attribute enquiry_attribute_5 ON central_enquiry.enquiry_number = enquiry_attribute_5.enquiry_number 
                                                AND enquiry_attribute_5.param_type_code = 'S818'
INNER JOIN sw_header ON sw_header.work_header_no = actual_inspection.work_header_no
INNER JOIN actual_inspection 
    ON actual_inspection.work_header_no = sw_header.work_header_no
INNER JOIN (
    SELECT 
        ce.enquiry_number,
        MAX(ai.logged_time) AS max_logged_time
    FROM actual_inspection ai
    JOIN central_enquiry ce ON ai.work_header_no = ce.work_header_no
    GROUP BY ce.enquiry_number
) latest_insp ON central_enquiry.enquiry_number = latest_insp.enquiry_number 
              AND actual_inspection.logged_time = latest_insp.max_logged_time
LEFT OUTER JOIN sw_notice_header ON sw_header.work_version_no = sw_notice_header.work_version_no 
                                AND sw_header.work_header_no = sw_notice_header.work_header_no
LEFT OUTER JOIN sw_site ON sw_site.work_header_no = sw_notice_header.work_header_no 
                       AND sw_site.site_number = sw_notice_header.primary_site_no
LEFT OUTER JOIN sw_notice_site ON sw_site.work_header_no = sw_notice_site.work_header_no 
                              AND sw_site.work_version_no = sw_notice_site.work_version_no 
                              AND sw_site.site_number = sw_notice_site.site_number
LEFT OUTER JOIN central_site ON sw_notice_site.site_code = central_site.site_code
LEFT OUTER JOIN locality ON central_site.locality_id = locality.locality_id
LEFT OUTER JOIN town ON locality.town_id = town.town_id
LEFT OUTER JOIN street_work_type ON sw_notice_header.street_work_code = street_work_type.street_work_code
LEFT OUTER JOIN area ON central_site.area_code = area.area_code
LEFT OUTER JOIN sw_agreement ON sw_agreement.work_version_no = sw_notice_header.work_version_no 
                            AND sw_agreement.work_header_no = sw_notice_header.work_header_no
LEFT OUTER JOIN organisation ON organisation.organise_code = sw_header.organise_code
LEFT OUTER JOIN organise_group ON organise_group.org_group_code = organisation.org_group_code
LEFT OUTER JOIN notice_type ON notice_type.notice_type_code = sw_notice_header.notice_type_code
LEFT OUTER JOIN sw_works_status ON sw_works_status.works_status_code = sw_notice_header.works_status_code
LEFT OUTER JOIN traffic_manage ON traffic_manage.traffic_code = sw_notice_site.traffic_code
LEFT OUTER JOIN sw_excavation ON sw_excavation.excavation_code = sw_notice_header.excavation_code
LEFT OUTER JOIN site_category ON site_category.category_code = central_site.category_code
LEFT OUTER JOIN program_insp ON program_insp.work_header_no = sw_header.work_header_no
WHERE
   sw_header.works_ref IN ('1303465')
   AND type_of_service.service_code = 'SW'
   AND enquiry_subject.subject_code = '06'
   AND enquiry_status.enq_status_code NOT IN ('50')

说明： #

• 子查询latest_insp先计算每个enquiry_number对应的最大logged_time。
• 主查询通过enquiry_number和logged_time关联，只保留最新时间的检查记录。
• 如果同一enquiry_number在同一时间有多条检查记录，这种方法会返回所有同时间的记录；若要仅返回一条，建议用方法一的窗口函数，可额外加排序字段（比如insp_number）确保唯一性。

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