Oracle SQL需求：按部门汇总不同状态的申请数量
2026-8-25
Oracle SQL行转列实现状态统计
样本数据
| App No | Dept | Status |
|---|---|---|
| 123 | IT | Pending |
| 124 | Sales | Closed |
| 456 | Sales | Declined |
| 821 | IT | Pending |
| 221 | IT | Closed |
| 876 | Sales | Pending |
| 665 | Sales | Closed |
期望输出
| Dept | Closed | Pending | Declined |
|---|---|---|---|
| IT | 1 | 2 | 0 |
| Sales | 2 | 1 | 1 |
实现SQL语句
SELECT Dept, COUNT(CASE WHEN Status = 'Closed' THEN 1 END) AS Closed, COUNT(CASE WHEN Status = 'Pending' THEN 1 END) AS Pending, COUNT(CASE WHEN Status = 'Declined' THEN 1 END) AS Declined FROM your_table_name GROUP BY Dept ORDER BY Dept;
说明
- 用
CASE WHEN针对不同状态做条件判断，符合条件返回1，否则返回NULL COUNT函数会自动忽略NULL值，以此统计各状态的数量- 通过
GROUP BY Dept按部门分组，得到每个部门的状态统计结果 - 替换
your_table_name为实际表名即可执行
内容的提问来源于stack exchange，提问作者Farhan
相关产品推荐
相关产品推荐
