SqlExceptionHelper : ERROR: function count(character varying, 整数)不存在

Question

我有一个 spring 应用程序，我有一个使用以下语法的 native 查询:

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;