一个表
TEMP有三个字段,大体记录如下
A B C
1 0 0
1 0 0
2 0 0
2 0 0
2 0 0
1 0 0
1 0 0
1 0 0
1 0 0
要用一个SQL语句查出以下的结果总条数 A字段1占百分比 A字段2占百分比数据量可能很多,请帮助写一条效率较高的SQL,谢谢
TEMP有三个字段,大体记录如下
A B C
1 0 0
1 0 0
2 0 0
2 0 0
2 0 0
1 0 0
1 0 0
1 0 0
1 0 0
要用一个SQL语句查出以下的结果总条数 A字段1占百分比 A字段2占百分比数据量可能很多,请帮助写一条效率较高的SQL,谢谢
是哪两个就写哪两个嘛/*
decode函数的用法
decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)
【功能】根据条件返回相应值
【参数】c1, c2, ...,cn,字符型/数值型/日期型,必须类型相同或null
注:值1……n 不能为条件表达式,这种情况只能用case when then end解决
*/
/*用你的来说明的话就是
decode(A,1,1,null) 如果A的值是1 那就用1 其他的用null
*/
select count(decode(A,1,1,null))/count(*) 1占的百分比,
count(decode(A,2,1,null))/count(*) 2占的百分比
from tbname
SQL> with tmp as
2 (
3 select 1 A, 0 B,0 C from dual
4 union all
5 select 1 A, 0 B,0 C from dual
6 union all
7 select 2 A, 0 B,0 C from dual
8 union all
9 select 2 A, 0 B,0 C from dual
10 union all
11 select 2 A, 0 B,0 C from dual
12 union all
13 select 1 A, 0 B,0 C from dual
14 union all
15 select 1 A, 0 B,0 C from dual
16 union all
17 select 1 A, 0 B,0 C from dual
18 union all
19 select 1 A, 0 B,0 C from dual
20 )
21 select count(decode(A,1,1,null))/count(*) "1占的百分比",
22 count(decode(A,2,1,null))/count(*) "2占的百分比"
23 from tmp;
1占的百分比 2占的百分比
----------- -----------
0.666666666 0.333333333
SQL> select * from tmp;
A B C
---------- ---------- ----------
1 0 0
1 0 0
2 0 0
2 0 0
2 0 0
1 0 0
1 0 0
1 0 0
1 0 0
9 rows selected
SQL> select distinct a, count(a)over(partition by a)/count(*)over() from tmp;
A COUNT(A)OVER(PARTITIONBYA)/COU
---------- ------------------------------
1 0.666666666666667
2 0.333333333333333
select count(decode(A,1,1,null))/count(*) 1占的百分比,
count(decode(A,2,1,null))/count(*) 2占的百分比
from tablename;
是不是存储过程不支持呀