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
uj5u.com熱心網友回復：
select count(*) count, type from table1 where value1>= lower and value1 <= upper group by typeuj5u.com熱心網友回復：
如果是count(*) =0 ,那么這個查詢出來的group好像不顯示，怎么能顯示？
uj5u.com熱心網友回復：
select count(*) count, type from table1 where value1>= lower and value1 <= upper group by typeunion
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)
uj5u.com熱心網友回復：
----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(+)
uj5u.com熱心網友回復：
----Type 為'D' 的不符合條件 count顯示0with 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
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/109231.html
標籤：開發