统计查询-根据条件进行count的两种实现方式- oracle
16lz
2021-01-22
Sql语句:
select b.departmentname, b.name,
count(case when b.businessstate='NOTSUBMIT' then 1 else null end ) NOTSUBMIT,
count(case when b.businessstate='ABANDONED' then 7 else null end ) ABANDONED,
count(b.id) allCounts
from business b group by b.departmentname, b.name
通过case when then else end这句话来限定条件,count进行统计。
另外,可以先建一张视图,通过sum来进行统计:
视图:
create or replace view view_business_statistics as
select b.id, b.name as BUSINESSNAME,b.DEPARTMENTNAME,b.sldate as SLDATE, b.applydate as APPLYDATE,
decode(b.businessstate,'NOTSUBMIT',1,0) as NOTSUBMITNUM,
decode(b.businessstate,'APPROVING',1,0) as APPROVINGNUM,
from business b;
通过sum进行统计的sql语句:
select b.departmentname, b.name,
sum(b.NOTSUBMITNUM) NOTSUBMIT,
sum(b.ABANDONED) ABANDONED,
count(b.id) allCounts
from business b group by b.departmentname, b.name
建立视图使用oracle自带的decode关键字(相当于case when),重新给列复制0和1,使用sum得到统计总数。
建立视图的思想是提高灵活性。
更多相关文章
- Mysql order by语句未使用索引的思考
- SQL语句练习(1)
- 自定义视图(组合控件)
- RecycleView的多视图Epoxy库
- android listview多视图嵌套多视图
- android应用如何在有需要时申请root权限?在程序中要调用哪些语句
- Android - Espresso -滚动到非列表视图项。
- 导航架构组件 - 具有CollapsingToolbar的详细信息视图
- 仪表测试自定义视图