sql - Oracle SQL 将来自同一个表的 2 个查询与 2 个不同列中的输出相结合
问题描述
我正在使用 Oracle SQL 数据库。我正在尝试将这两个查询合并为一个来自同一个表的查询:
select count(CustomerID) from Customers
where (City = 'Berlin' or City = 'London')
from Customers;
select count(CustomerID) from Customers
where (City = 'Mannheim' or City = 'Strasbourg')
from Customers;
我的输出希望类似于在 2 列上导出:
| Berlin & London | Mannheim & Strasbourg |
|-----------------------------------------|
| 5 | 10 |
这里的 db 示例,https: //www.w3schools.com/sql/trysql.asp?filename=trysql_op_in
我认为答案将是:
(select count(CustomerID) from Customers
where (City = 'Berlin' or City = 'London'))
union
(select count(CustomerID) from Customers
where (City = 'Marseille' or City = 'Tsawassen'));
union
(select count(CustomerID) from Customers
where (City = 'Strasbourg' or City = 'Madrid'));
还在测试...
终于通过使用这个案例完成了工作。感谢所有参与的人!
SELECT
COUNT(CASE
WHEN work_zone = 'AMBGI01' OR
work_zone = 'AMBGI02' OR
work_zone = 'AMBGI03' AND
task_type = 'P' THEN work_zone
END) "HighBay Putaways",
COUNT(CASE
WHEN work_zone = 'AMBGI04' OR
work_zone = 'AMBGI05' OR
work_zone = 'AMBGI06' OR
work_zone = 'AMBGI07' AND
task_type = 'P' THEN work_zone
END) "LowBay Putaways",
COUNT(CASE
WHEN from_loc_id = 'THEWALL' AND
task_type = 'P' THEN from_loc_id
END) "THE WALL",
COUNT(CASE
WHEN tag_id like '%' AND
from_loc_id like 'EXPANS%' AND
task_type = 'P' THEN tag_id
END) "EXPANSION",
COUNT(CASE
WHEN final_loc_id like '_______' AND
(status = 'In Progress' OR
status = 'Released') AND
task_type = 'R' THEN final_loc_id
END) "Replens"
FROM move_task;
解决方案
最简单的方法如下:
select count(CASE WHEN City = 'Berlin' or City = 'London' THEN City END) "Berlin & London"
, count(CASE WHEN City = 'Mannheim' or City = 'Strasbourg' THEN City END) "Mannheim & Strasbourg"
from Customers
推荐阅读
- java - 返回 MembersInjectors.injectMembers 时出错
- python - FFmpeg 错误:输出文件为空,未编码
- amazon-web-services - 卡夫卡没有通过互联网连接
- shell - 从另一个 shell 脚本访问 slurm EXIT 代码
- c# - 单击展开按钮我想在 Xamarin 表单中向下滚动页面
- css - 在 CSS 中混合 @supports 和 @media 查询
- java - 是否可以将 Java double 转换为 Python float?
- vba - 将美国(默认)日期格式转换为英国
- jboss - JBOSS/Wildfly 集群和 Kubernetes -
- msbuild - PackageReference 的条件包含