2.
select c.change_count,c.file_count,d.count*100/d.sum
from
(select a.count change_count,count(*) file_count
from
(select change_file,count(*)-1 as COUNT
from test_change
group by change_file) a,
(select change_file,count(*)-1 as COUNT
from test_change
group by change_file) b
where a.count = b.count
group by a.count) c,
(select count(*) sum,count(destinct change_file) count from test_change )d
order by c.change_count asc;
select c.change_count,c.file_count,d.count*100/d.sum
from
(select a.count change_count,count(*) file_count
from
(select change_file,count(*)-1 as COUNT
from test_change
group by change_file) a,
(select change_file,count(*)-1 as COUNT
from test_change
group by change_file) b
where a.count = b.count
group by a.count) c,
(select count(*) sum,count(destinct change_file) count from test_change )d
order by c.change_count asc;
解决方案 »
- sum中出现then 1 else 0的的含义
- PL/SQL小問題
- sqlserver 升级到oracle
- 请教:ORA-03115: 不支持的网络数据类型或表示法
- 一个表查询速度特慢,是什么原因?
- 如何将一个数据库中表1的内容转移到另一个数据库相同结构的表2中
- 如何解决这个ora:00600的错误?
- 紧急求助(在线等):为什么删除了profile的终止口令,登陆时还是提示口令有效期并要求修改密码?
- 如何使用触发器实现一个表单中的自增长列?
- 大家帮我理解这一句
- 请问怎样在oracle9i中创建一个新的数据库?(使用客户端登陆)谢谢大家了!
- rh8+oracle9i中oracle的启动问题! 急!
select a.change_date,a.a_count,a.a_count/d.sum,a.count
from
(select change_date,sum(a.count) a_count,count
from
(select change_date,count(*)-1 as count
from test_change
group by change_date)
group by change_date) a,
(select count(*) sum from test_change )d
order by a.change_date asc;
select a.l_date,a.a_count,a.a_count/d.sum,a.count
from
(select b.l_date l_date,b.count count,count(b.count) a_count
from
(select change_date l_date,count(*)-1 as count
from test_change
group by change_date)b,
(select change_date l_date from test_change )c
where b.l_date>=c.l_date
group by b.l_date,
b.count ) a,
(select count(*) sum from test_change )d,
order by a.change_date asc;
d表的写法也好像有点问题
在oracle上不能运行
from
(select change_date l_date,count(*) as sum_count
from test_change
group by change_date) a, (select l_date,count(num) new_num
from (select change_date l_date,change_file,count(*) as num
from test_change
group by change_date,change_file)
where num = 1
group by l_date) b, (select l_date,count(num) modify_num
from (select change_date l_date,change_file,count(*) as num
from test_change
group by change_date,change_file)
where num >1
group by l_date) c
where a.l_date = b.l_date
and a.l_date = c.l_date;
4.
select a.l_date,b.new_num,b.new_num/a.sum_count,c.modify_num,c.modify_num/a.sum_count
from
(select change_date l_date,count(*) as sum_count
from test_change
group by change_date) a, (select l_date,sum(num) new_num
from (select change_date l_date,change_file,count(*) as num
from test_change
group by change_date,change_file)
where num = 1
group by l_date) b, (select l_date,sum(num) modify_num
from (select change_date l_date,change_file,count(*) as num
from test_change
group by change_date,change_file)
where num >1
group by l_date) c
where a.l_date = b.l_date
and a.l_date = c.l_date;
select aaa.COUNT,Count(aaa.COUNT) AS N, to_char(Count(aaa.COUNT)/Avg(bbb.TOTAL)*100,'00.00') AS R from
(select change_file,count(*)-1 as COUNT from test_change group by change_file order by COUNT asc) aaa,
(select count(*) as TOTAL from (select count(*)-1 as COUNT from test_change group by change_file order by COUNT asc)) bbb
group by aaa.COUNT
我再看看3.4哦
select a.change_user,trunc(a.count/b.count,1)
from
(select change_user,count(*) count
from test_change
group by change_user)a,
(select change_user,count(*) count
from
(select change_user,change_date
from test_change
group by change_user,change_date
)
group by change_user)bwhere a.change_user = b.change_user
order by a.change_user asc;