sql - 使用 case 语句时，PostgreSQL 列必须出现在 GROUP BY 子句中或用于聚合函数中

Question

我正在使用 case 语句计算两列，primary_specialty 和 secondary_specialty。这很好用，但是，我想在 pd.id 上执行 GROUP BY 并收到以下错误:

列“pppd.created_at”必须出现在 GROUP BY 子句中或用于聚合函数。

所需的输出是每个 pd 的一行，其中有一列用于 primary_specialty 和 secondary_specialty

SELECT pd.id,
       pd.npi,
       pppd.created_at AS "date_submitted", 
       pppd.converted_at AS "date_approved", 
       dp.created_at AS "date_profile_created",
       t.description AS "npi_specialty",
       case when ds.ordinal = 1 then s.name end as "primary_specialty",
       case when ds.ordinal = 2 then s.name end as "secondary_specialty"

FROM potential_doctors AS pd
     INNER JOIN patient_profile_potential_doctors as pppd on pd.id = pppd.potential_doctor_id
     INNER JOIN doctor_taxonomies AS dt on pd.id = dt.potential_doctor_id
     INNER JOIN taxonomies AS t on dt.taxonomy_id = t.id
     INNER JOIN doctor_profiles AS dp on pd.npi = dp.npi
     INNER JOIN doctor_specialties AS ds on dp.id = ds.doctor_profile_id
     INNER JOIN specialties AS s on ds.specialty_id = s.id

Answer 1

使用DISTINCT ON (pd.id) 而不是GROUP BY

SELECT DISTINCT ON (pd.id) pd.id,
           pd.npi,
           pppd.created_at AS "date_submitted", 
           pppd.converted_at AS "date_approved", 
           dp.created_at AS "date_profile_created",
           t.description AS "npi_specialty",
           case when ds.ordinal = 1 then s.name end as "primary_specialty",
           case when ds.ordinal = 2 then s.name end as "secondary_specialty"

    FROM potential_doctors AS pd
         INNER JOIN patient_profile_potential_doctors as pppd on pd.id = pppd.potential_doctor_id
         INNER JOIN doctor_taxonomies AS dt on pd.id = dt.potential_doctor_id
         INNER JOIN taxonomies AS t on dt.taxonomy_id = t.id
         INNER JOIN doctor_profiles AS dp on pd.npi = dp.npi
         INNER JOIN doctor_specialties AS ds on dp.id = ds.doctor_profile_id
         INNER JOIN specialties AS s on ds.specialty_id = s.id