SqlExceptionHelper：错误：函数计数（字符变化，整数）不存在

我有一个 spring 应用程序，并且有一个具有以下语法的本机查询：

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;   

我收到错误：

 ERROR: function count(character varying, integer) does not exist

我使用 PostgreSQL 作为数据库。我提到在 mysql 中，查询是有效的。

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;