Postgres不支持count()
多于一列。但是,您可以使用以下类似的方法将两列简单地转换为匿名记录类型(col_one, col_two)
的单列:-这是匿名记录类型的单列。
select COUNT(DISTINCT (person.id,(CASE WHEN salary_person.rating = 'Satisfactory' THEN 1 END))) AS totalSatisfactory,
COUNT(DISTINCT (person.id,(CASE WHEN salary_person.rating = 'Unsatisfactory' THEN 1 END))) AS totalUnsatisfactory
from person
join salary_person on person.id = salary_person.person_id;
注意两列的括号。
但是,在Postgres中,通过在filter
子句中使用条件聚合,您可以采用一种更优雅的方式来执行所需的操作:
select COUNT(DISTINCT person.id) filter (where salary_person.rating = 'Satisfactory') AS totalSatisfactory,
COUNT(DISTINCT person.id) filter (where salary_person.rating = 'Unsatisfactory') AS totalUnsatisfactory
from person
join salary_person on person.id = salary_person.person_id;