SqlExceptionHelper：错误：函数计数（字符变化，整数）不存在 [英] SqlExceptionHelper : ERROR: function count(character varying, integer) does not exist

问题描述

我有一个spring应用程序，我有一个使用以下语法的本地查询：

I have a spring application and I have a native query with this syntax:

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

As database I use PostgreSQL. I mention that in mysql, the query is working.

推荐答案

Postgres不支持 count（）包含不止一列。但是，您可以使用以下方法将两个列都简单地转换为匿名记录类型的单个列：（col_one，col_two）-这是匿名记录的单个列记录类型。

Postgres does not support count()with more than one column. You can however simply turn both columns into a single column of an anonymous record type, by using something like: (col_one, col_two) - that is a single column of an an anonymous record type.

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;   

请注意两栏中的括号。

Note the parentheses around the two columns.

但是，在Postgres中，您可以通过将条件聚合与 filter 子句：

However, in Postgres you have a more elegant way to do what you want, by using conditional aggregation with the filter clause:

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;   

这篇关于SqlExceptionHelper：错误：函数计数（字符变化，整数）不存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！