本文介绍了postgreSQL组或不是的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述



以下代码重现:

  name | time_ | 
-----------------------------------
stackoverflow 3:00
google 5:35
stackoverflow 1:00

SELECT agenc.name,(SELECT CAST(servic.end_hour as time) - CAST(servic.begin_hour as time))AS time_,jobs .name,
FROM服务AS服务
JOIN services_jobs AS job on jobs.id = servic.job_id
JOIN代理机构AS agenc ON agenc.id = jobs.agency_id
WHERE
EXTRACT(MONTH FROM servic.service_date)= 9 AND
EXTRACT(日期从servic.service_date)= 16

我希望它以这种方式出现:

  name | time_ | 
-----------------------------------
stackoverflow 4:00
google 5:35

可能吗?如果是这样,怎么样?



我曾经想过分组,但是我没有得到任何结果。

解决方案

很难在这里帮助,我想你正在寻找 sum group by

  select 
agenc.name,
sum(cast(servic.end_hour as time) - cast (servic.begin_hour as time))as time_
from
services as servic
join services_jobs AS jobs on jobs.id = servic.job_id
加入agency as agenc ON agenc.id = jobs.agency_id
其中
提取(月份来自servic.service_date)= 9和
提取(日期自servic.service_date)= 16
组合
agenc。名称


I'm trying to group by the name the results.

The following code reproduces:

    name           |    time_     |   
-----------------------------------
    stackoverflow       3:00
    google              5:35
    stackoverflow       1:00

SELECT agenc.name, (SELECT CAST(servic.end_hour as time) - CAST(servic.begin_hour as time)) AS time_, jobs.name,
FROM services AS servic  
JOIN services_jobs AS jobs ON jobs.id = servic.job_id  
JOIN agency AS agenc ON agenc.id = jobs.agency_id
WHERE  
EXTRACT(MONTH FROM servic.service_date) = 9 AND  
EXTRACT(DAY FROM servic.service_date) = 16

And I want it to appears this way:

    name           |    time_     |   
-----------------------------------
    stackoverflow       4:00
    google              5:35

Is it possible? If so, how?

I thought about grouping, but I didn't get any result.

解决方案

Difficult to help here, I guess you are looking for sum and group by:

select 
  agenc.name, 
  sum(cast(servic.end_hour as time) - cast(servic.begin_hour as time)) as time_
from 
  services as servic  
  join services_jobs AS jobs ON jobs.id = servic.job_id  
  join agency as agenc ON agenc.id = jobs.agency_id
where  
  extract(month from servic.service_date) = 9 and  
  extract(day from servic.service_date) = 16
group by
  agenc.name

这篇关于postgreSQL组或不是的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

10-30 19:23