是否可以在postgresql中运行具有两个不同列的case语句?
我想使用 CASE
语句将整数映射到特定值。我可以用ds.ordinal
做到这一点,但是当我添加dcs.status
时,我收到以下错误:column "follow_up" does not exist
.
是否可以在一个 SELECT 语句中对不同列使用多个 case 语句?
如何让case when dcs.status = 0 then "follow_up" end as "follow_up"
不返回错误?
SELECT DISTINCT ON (pd.id)
case when ds.ordinal = 1 then s.name end as "primary_specialty",
case when ds.ordinal = 2 then s.name end as "secondary_specialty",
case when dcs.status = 0 then "follow_up" end as "follow_up"
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
INNER JOIN doctor_creation_notes as dcs on dcs.doctor_profile_id = dp.id
WHERE dp.approved IS FALSE
我希望查询看起来像这样;
SELECT DISTINCT ON (pd.id)
(case when ds.ordinal = 1 then s.name end) as primary_specialty,
(case when ds.ordinal = 2 then s.name end) as secondary_specialty,
(case when dcs.status = 0 then 'follow_up' end) as follow_up
FROM . . .
WHERE dp.approved IS FALSE
ORDER BY pd.id;
换句话说,我认为您只需要在字符串常量周围使用单引号。
我做了另外两个更改。 如果您使用的是 DISTINCT ON
,那么您应该有一个ORDER BY
,将DISTINCT ON
括号中的表达式作为ORDER BY
中的第一个键。 您可以添加更多键以获取最早、最新、最大、最小或您特别想要的任何行。
此外,我从列名称中删除了双引号。 您显然对SQL中的引号感到困惑。 我的建议是仅对字符串和日期常量使用单引号。 不要使用双引号,并通过仅使用有效字符为标识符命名来避免使用双引号。