数据源为:
类别 时间 状态
A 08:00 0
A 09:00 1
A 10:00 1
A 11:00 1
A 12:00 0
A 13:00 1
A 14:00 0
A 14:00 0
A 14:00 0
A 14:00 0希望得到结果:
类别 状态0 状态1 总数 状态0与总数比
A 6 4 10 60%
类别 时间 状态
A 08:00 0
A 09:00 1
A 10:00 1
A 11:00 1
A 12:00 0
A 13:00 1
A 14:00 0
A 14:00 0
A 14:00 0
A 14:00 0希望得到结果:
类别 状态0 状态1 总数 状态0与总数比
A 6 4 10 60%
解决方案 »
- 求助!!Oracle里如何判断NULL?
- 求oracle语句
- Apache、ORACLE、PHP都已经正确安装配置完成,初次运行sugarCRM,系统会自动进入安装配置页面,为什么配置PHP的时候只有MYSQL没有ORACLE选项?在线等,谢谢
- java 存储过程 编码
- 那种数据库更适合我的需求?
- oracle序列的相关问题,还请大家多多指教.
- Oracle数据导入问题
- 主键索引一定是number列吗
- 求助!!
- zhaoyongzhu进来领分2
- 各位高手指点下小弟,小弟遇到问题,还请各位帮帮忙select'编号是:'‖empno‖'的雇员,姓名是:'‖ename‖',工作是:'‖job from emp
- JOIN的问题,请教一下
select 类别,
sum(case when 状态=0 then 1 else 0 end) as 状态0,
sum(case when 状态=1 then 1 else 0 end) as 状态1,
sum(case when 状态=0 then 1 else 0 end)/count(1) as 状态0与总数比
from tablename
group by 类别
SELECT SORT,COUNT(DECODE(STATS,0,STATS)) STATS0,COUNT(DECODE(STATS,1,STATS)) STATS1,COUNT(STATS) STATS,(COUNT(DECODE(STATS,0,STATS))/COUNT(STATS))*100||'%' HJ FROM TESTING GROUP BY SORT
--基础数据
CREATE TABLE table1(
type varchar2(10),
time1 DATE,
status number(1)
);
insert into table1(type,time1,status) values('A',sysdate,'0');
insert into table1(type,time1,status) values('A',sysdate,'1');
insert into table1(type,time1,status) values('A',sysdate,'1');
insert into table1(type,time1,status) values('A',sysdate,'0');
insert into table1(type,time1,status) values('A',sysdate,'1');COMMIT;
--查询语句SELECT a.type 类型,
COUNT(DECODE(a.status, 0, 1)) 状态0,
COUNT(DECODE(a.status, 1, 1)) 状态1,
COUNT(a.status) 状态总数,
(COUNT(DECODE(a.status, 0, 1)) / COUNT(1)) * 100 || '%' 状态0与总数比
FROM table1 a
GROUP BY a.type;
with t as (
select 'A' "类别",'08:00' "时间",'0' "状态" from dual
union all
select 'A','09:00','1' from dual
union all
select 'A','10:00','1' from dual
union all
select 'A','11:00','1' from dual
union all
select 'A','12:00','0' from dual
union all
select 'A','13:00','1' from dual
union all
select 'A','14:00','0' from dual
union all
select 'A','14:00','0' from dual
union all
select 'A','14:00','0' from dual
union all
select 'A','14:00','0' from dual
)
select "类别",
sum(decode("状态", '0', 1)) "状态0",
sum(decode("状态", '1', 1)) "状态1",
count(1) "总数",
sum(decode("状态", '0', 1)) * 100 / count(1) || '%' "状态0与总数比"
from t
group by "类别"
类别 状态0 状态1 总数 状态0与总数比
---- ---------- ---------- ---------- -----------------------------------------
A 6 4 10 60%
sum(decode("状态", '0', 1)) "状态0",
sum(decode("状态", '1', 1)) "状态1",
count(1) "总数",
sum(decode("状态", '0', 1)) * 100 / count(1) || '%' "状态0与总数比"
from t
group by "类别"
sum(decode(状态, 0, 1, 0)) 状态0,
sum(decode(状态, 1, 1, 0)) 状态1,
count(1) 总数,
sum(decode(状态, 0, 1, 0)) / count(1) 状态0占总数比,
to_char(round((sum(decode(状态, 0, 1, 0)) / count(1))*100,2),'00.00') || '%' --转化成百分数
from tab_A
group by 类别;