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

Question

我有一个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中，查询有效。

Answer 1

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;