PostgreSQL使用case语句时，列必须出现在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

- user2954587

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

- Sergey Gershkovich

Answer 2

这可能是您想要的查询语句：

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",
       max(case when ds.ordinal = 1 then s.name end) as "primary_specialty",
       max(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
GROUP BY pd.id, pd.npi, pppd.created_at,  pppd.converted_at, t.description

- Gordon Linoff

Answer 3

如果您按一个或多个列进行分组，则只能选择这些列或聚合函数。

因为您正在对许多行进行分组，所以每个pd.id可能会有许多“primary_specialty”。但是，如果您从数据库模式中假定集合中只有一个不同的值，那么您可以使用聚合函数（如MAX）来获取所需的值。类似于这样：

SELECT pd.id,
       MAX(case when ds.ordinal = 1 then s.name end) as "primary_specialty",
       MAX(case when ds.ordinal = 2 then s.name end) as "secondary_specialty"

- Alex Zen