How the Oracle of the current row in three fields to compare size, choose the qualified data?-CodePudding

Oracle has the following table:
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

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 B

0.33 0.05 0.79 C
0.22 0.21 0.79 C
0.98 0.05 1.00 C
.

I want to find out value1 & gt;=the lower and value1 & lt;=upper, and carried out in accordance with the type type statistics,
Results:
The count type
2 A
2 B
3 C

CodePudding user response:

Select count (*) count, type the from table1 where value1 & gt;=the lower and value1 & lt;=upper group by type

CodePudding user response:

reference 1st floor ZBDZJX response:

select count (*) count, type the from table1 where value1 & gt;=the lower and value1 & lt;=upper group by type

If it is the count (*)=0, so the query doesn't seem to show group, how to show?

CodePudding user response:

Select count (*) count, type the from table1 where value1 & gt;=the lower and value1 & lt;=upper group by type
The union
Select 0, t2. Type the from table1 t2 where t2. The type not in (
The select t3. Type the from table1 t3 where t3. Value1 & gt;=t3. The lower and t3. Value1 & lt;=t3. Upper)

CodePudding user response:

 
- Type as' D 'is not in conformity with the conditions of the count display 0 
With t as 
(select the 'A' type, 0.11 value1, 0.05 the lower, upper 0.89 
The from dual 
Union all 
Select the 'A', 0.23, 0.11, 0.99, upper 
The from dual 
Union all 
Select the 'A', 0.81, 0.1, 0.34, upper 
The from dual 
Union all 
Select 'B', 0.11, 0.75, 0.99, upper 
The from dual 
Union all 
Select 'B', 0.34, 0.05, 0.89, upper 
The from dual 
Union all 
Select 'B', 0.45, 0.01, 0.66, upper 
The from dual 
Union all 
Select 'B', 0.01, 0.05, 0.72, upper 
The from dual 
Union all 
Select the 'C', 0.33, 0.05, 0.79 upper 
The from dual 
Union all 
Select the 'C', 0.22, 0.21, 0.79 upper 
The from dual 
Union all 
Select the 'C', 0.98, 0.05, 1.00 upper 
The from dual 
Union all 
Select the 'D', 0.98, 0.99, 1.00 upper from dual) 
The select NVL (count, 0) count, t2. Type the 
The from (select count (*) count, type 
The from t 
Where value1 & gt;=the lower 
And value1 & lt;=upper 
Group by type) t1, 
(select distinct type from t) t2 
Where t2) type=t1) type (+)

CodePudding user response:

- Type as' D 'is not in conformity with the conditions of the count display 0
With t as
(select the 'A' type, 0.11 value1, 0.05 the lower, upper 0.89
The from dual
Union all
Select the 'A', 0.23, 0.11, 0.99, upper
The from dual
Union all
Select the 'A', 0.81, 0.1, 0.34, upper
The from dual
Union all
Select 'B', 0.11, 0.75, 0.99, upper
The from dual
Union all
Select 'B', 0.34, 0.05, 0.89, upper
The from dual
Union all
Select 'B', 0.45, 0.01, 0.66, upper
The from dual
Union all
Select 'B', 0.01, 0.05, 0.72, upper
The from dual
Union all
Select the 'C', 0.33, 0.05, 0.79 upper
The from dual
Union all
Select the 'C', 0.22, 0.21, 0.79 upper
The from dual
Union all
Select the 'C', 0.98, 0.05, 1.00 upper
The from dual
Union all
Select the 'D', 0.98, 0.99, 1.00 upper from dual)
The select T.T YPE, SUM (CASE WHEN T.V ALUE1 & gt;=T.L power AND T.V ALUE1 & lt;=T.U PPER THEN 1 ELSE 0 END) COUNT from T group by T.T YPE order by T.T YPE