代码之家 › 专栏 › 技术社区 › mamesaye

PSQL9.1错误:列必须出现在GROUPBY子句中或在聚合函数中使用

string-aggregation postgresql-9.1 integer group-by arrays

1

mamesaye · 技术社区 · 7 年前

我有3个表格:摘要,部分,人。

 people

      Column      |            Type             |                     
------------------+-----------------------------+
 id               | bigint                      |
 last_name        | character varying           | 
 middle_name      | character varying           | 
 first_name       | character varying           | 
 current_position | character varying           | 

 summaries

     Column     |            Type             |                       
----------------+-----------------------------+
 id             | bigint                      | 
 channel        | character varying           | 
 show           | character varying           | 
 seg_ids        | character varying           | segment ids
 date           | timestamp without time zone | 
 start_time     | timestamp without time zone | 
 end_time       | timestamp without time zone | 

segments

       Column       |            Type             |                                     
--------------------+-----------------------------+
 id                 | bigint                      | 
 block_id           | integer                     | 
 person_id          | bigint                      | foreign key (FK)
 person_role        | character varying           | 
 summ               | integer                     | summary id FK
 deleted            | boolean                     |

我的表summaries有一列seg\u ids(segments ids),它是一个整数字符串,我可以用这个查询将它转换成一个整数数组,返回一千多个id:

select regexp_split_to_array((select rtrim(ltrim(replace((select string_agg(seg_ids, ', ')), '], [', ', '), '['),']') from summaries where date between '2018-07-04' and '2018-07-06'),',')::int[];

现在有了整数数组(seg\u id),我想用它来显示所有的摘要和连接到段的人。我尝试此查询失败:

SELECT summ, block_id, person, seg.id as segid, su.channel, su.show, date::timestamp::date as shdate, "time"(su.start_time) as shst, CONCAT (ppl.last_name, ', ', ppl.first_name) AS full_name, substr(person_role, 1, 2) as person_role 
FROM segments seg    
LEFT JOIN summaries su on seg.summ = su.id    
LEFT JOIN people ppl  on ppl.id = person_id    
HAVING seg.id::int = any (  
   regexp_split_to_array((    
      SELECT   
         rtrim(ltrim(  
             replace(   
                string_agg(seg_ids, ', ')   
             , '], [', ', ')  
        , '['),']')    
      FROM summaries    
      WHERE date between '2018-07-04' and '2018-07-06')   
   ,',')::int[])  order by shdate, channel, shst, show, su.id, block_id, person,seg.id asc;

这给了我一个错误:

ERROR:  column "seg.summ" must appear in the GROUP BY clause or be used in an aggregate function

更新
person在segments表中。

SELECT summ,
   block_id,
   person,
   seg.id as segid, 
   su.channel, su.show,
   date::TIMESTAMP::date AS shdate,
   "time"(su.start_time) AS shst,
   "time"(su.end_time) AS shet,
   regexp_split_to_array(rtrim(ltrim(su.seg_ids, '['), ']'), ',')::int[] AS seg_id_int_arr,
   CONCAT (ppl.last_name, ', ', ppl.first_name) AS full_name,
   substr(person_role, 1, 2) AS person_role
FROM summaries su
LEFT JOIN segments seg ON seg.summ = su.id
LEFT JOIN people ppl ON ppl.id = person_id
WHERE date BETWEEN '2018-07-04' AND '2018-07-06'
 AND seg.id::int = ANY (regexp_split_to_array(rtrim(ltrim(seg_ids, '['), ']'), ',')::int[])
ORDER BY shdate, channel, shst, show, su.id, block_id, person, seg.id ASC;

1 回复 | 直到 7 年前

1

Shubham Srivastava 7 年前

你应该知道何时使用having/where子句。Where子句用于在执行聚合之前筛选行(可以使用GROUPBY或其他聚合函数)。而Having子句用于在执行聚合后过滤数据。你可以参考这个 explanation
我看到segment表已经有了summaries的id和personid,所以您试图在having子句中实现的最后一个条件可以用join简化。据我所知,您需要所有的段信息,其中摘要位于特定的日期范围内,这可以通过简单地改进我们的连接条件来完成,如
```
FROM segments seg    
JOIN summaries su on seg.summ = su.id  
AND su.date between '2018-07-04' and '2018-07-0`6'`
LEFT JOIN people ppl on ppl.id = person_id
```
我还看到您选择了一个名为person的列,但我在您的任何表中都看不到它