postgresql - Postgresql - 如何组合这两个查询
问题描述
我尝试将这两个查询合二为一。
这些查询的结果是给定运营商接受/拒绝的申请数量。
我想在三列中得到这样的结果:接受申请的数量、被拒绝的申请数量和分配给它的操作员。
select count(applications.id) as number_of_applications, operator_id
from applications
inner join travel p on applications.id = p.application_id
inner join trip_details sp on p.id = sp.trip_id
where application_status ilike '%rejected%'
group by operator_id
order by number_of_applications desc;
select count(applications.id) as number_of_applications, operator_id
from applications
inner join travel p on applications.id = p.application_id
inner join trip_details sp on p.id = sp.trip_id
where application_status ilike '%accepted%'
group by operator_id
order by number_of_applications desc;
解决方案
使用条件聚合:
select
sum(case when application_status ilike '%accepted%' then 1 else 0 end) as number_of_applications_accepted,
sum(case when application_status ilike '%rejected%' then 1 else 0 end) as number_of_applications_rejected,
operator_id
from applications
inner join travel p on applications.id = p.application_id
inner join trip_details sp on p.id = sp.trip_id
where (application_status ilike '%rejected%') or (application_status ilike '%accepted%')
group by operator_id;
您可以添加您喜欢的顺序。
推荐阅读
- android - How can Activity and its Views lifecycles overlap in android
- java - 将时间向后移动几秒钟时的输出错误
- python - Random seed for order of elements in Python's Set to List conversion
- arrays - WebdriverJS Expect Arrays to be equal ignoring order
- python - Pandas: filling NaNs only in rows intervals for which lower bound is given
- reactjs - 如何在 gatsby 中使用谷歌标签管理器
- javascript - node_modules 包导入上的意外标识符
- angular - 如何在模板驱动的表单中获取表单组验证结果?
- c++ - 有什么方法可以创建用户定义的类型修饰符?
- python - 为什么随机数永远不会等于 4.0?