oracle中怎么对count求和,Oracle 集合函数(count,sum等)和case when的组合使用

有没有遇到过表中某字段是状态字段或类型字段,

然后需要统计各种状态或类型的记录数,这个时候一般是怎样实现呢?

假设表A,有1个字段是状态字段,状态值有1,2,3

如果没有查询条件可以这样,如果查询条件很复杂时,这个语句就没法看了

如果涉及到表联合,group by ,那就更复杂了

select

(select count(*) from A where state=1 and 其他条件 ) as state1,

(select count(*) from A where state=2 and 其他条件 ) as state2,

(select count(*) from A where state=3 and 其他条件 ) as state3,

from dual

执行了3次表查询

改进后:

select

count(case when state= 1 then state end) as state_1,

count(case when state= 2 then state end) as state_2,

count(case when state= 3 then state end) as state_3

from A

where state=1 and 其他条件

只需要一次表查询, 语句越复杂,使用集合函数和case when的组合就越精简,越高效