本文介绍了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组或不是的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!