oracle中有如下表:
type value1 lower upper
A 0.11 0.05 0.89
A 0.23 0.11 0.99
A 0.81 0.1 0.34 B 0.11 0.75 0.99
B 0.34 0.05 0.89
B 0.45 0.01 0.66
B 0.01 0.05 0.72 C 0.33 0.05 0.79
C 0.22 0.21 0.79
C 0.98 0.05 1.00
...我想找出value1 >= lower ,并且value1 <= upper,并且按照type类型进行统计。
结果:
count type
2 A
2 B
3 C
type value1 lower upper
A 0.11 0.05 0.89
A 0.23 0.11 0.99
A 0.81 0.1 0.34 B 0.11 0.75 0.99
B 0.34 0.05 0.89
B 0.45 0.01 0.66
B 0.01 0.05 0.72 C 0.33 0.05 0.79
C 0.22 0.21 0.79
C 0.98 0.05 1.00
...我想找出value1 >= lower ,并且value1 <= upper,并且按照type类型进行统计。
结果:
count type
2 A
2 B
3 C
解决方案 »
- txt 导入 oracle的问题
- 菜鸟级提问,一条SQL语句,不知道如何修改才能满足需求[请高手看看]
- 求个SQL
- 这样的脚本在Oracle中为什么就不能执行?
- 9i,使用manager console,用登录到management server,在managerment server中填写了我机器的ip或机器名,用sysman/oem_temp,提示我输入
- 求助:关于Oracle9i中的全文索引的问题!!!来者有分!!!谢了先!!!
- 有关Oracle的关联问题?
- oracle 左链接,右链接问题
- sql server 与oracle的数据类型
- asp 连接 ocacle 报错
- SQL问题处理。
- sql loader导入数据如何把上传文件夹名作为一列插入,文件夹里是需要的txt文件
如果是count(*) =0 ,那么这个查询出来的group好像不显示,怎么能显示?
union
select 0,t2.type from table1 t2 where t2. type not in (
select t3. type from table1 t3 where t3.value1>= t3.lower and t3.value1 <=t3. upper)
----Type 为'D' 的不符合条件 count显示0
with t as
(select 'A' type, 0.11 value1, 0.05 lower, 0.89 upper
from dual
union all
select 'A', 0.23, 0.11, 0.99 upper
from dual
union all
select 'A', 0.81, 0.1, 0.34 upper
from dual
union all
select 'B', 0.11, 0.75, 0.99 upper
from dual
union all
select 'B', 0.34, 0.05, 0.89 upper
from dual
union all
select 'B', 0.45, 0.01, 0.66 upper
from dual
union all
select 'B', 0.01, 0.05, 0.72 upper
from dual
union all
select 'C', 0.33, 0.05, 0.79 upper
from dual
union all
select 'C', 0.22, 0.21, 0.79 upper
from dual
union all
select 'C', 0.98, 0.05, 1.00 upper
from dual
union all
select 'D', 0.98, 0.99, 1.00 upper from dual)
select nvl(count, 0) count, t2.type
from (select count(*) count, type
from t
where value1 >= lower
and value1 <= upper
group by type) t1,
(select distinct type from t) t2
where t2.type = t1.type(+)
with t as
(select 'A' type, 0.11 value1, 0.05 lower, 0.89 upper
from dual
union all
select 'A', 0.23, 0.11, 0.99 upper
from dual
union all
select 'A', 0.81, 0.1, 0.34 upper
from dual
union all
select 'B', 0.11, 0.75, 0.99 upper
from dual
union all
select 'B', 0.34, 0.05, 0.89 upper
from dual
union all
select 'B', 0.45, 0.01, 0.66 upper
from dual
union all
select 'B', 0.01, 0.05, 0.72 upper
from dual
union all
select 'C', 0.33, 0.05, 0.79 upper
from dual
union all
select 'C', 0.22, 0.21, 0.79 upper
from dual
union all
select 'C', 0.98, 0.05, 1.00 upper
from dual
union all
select 'D', 0.98, 0.99, 1.00 upper from dual)
select T.TYPE,SUM(CASE WHEN T.VALUE1>=T.LOWER AND T.VALUE1<=T.UPPER THEN 1 ELSE 0 END ) COUNT from T group by T.TYPE order by T.TYPE