Pray god to solve a case when problems in oracle-CodePudding

If 30-35, temporarily no data but be displayed, the total display is 0, a great god, please give directions

 select case 
The when t.n l & lt; 30 then 
'30 years old the following (not including 30) 
The when t.n l & gt;=30 and t.n l & lt; 35 then=
'30-35' 
The when t.n l & gt;=36 and t.n l & lt; 40 then=
'36 to 40' 
The when t.n l & gt;=41 and t.n l & lt; 45 then=
'41-45' 
The when t.n l & gt;=46 and t.n l & lt;=50 then 
'46-50' 
The when t.n l & gt; 51 and t.n=l & lt;=55 then 
'51 and 55' 
The when t.n l & gt;=56 and t.n l & lt; 60 then=
'56-60' 
The when t.n l & gt;=61 and t.n l & lt; Then=65 
'61-65 
The when t.n l & gt;=66 and t.n l & lt; Then=69 
'66-69 
The when t.n l & gt; Then=70 
'more than 70 (including 70) 
End as lx, 
Count (1) the as sumper 


The from (select rownum, 
T1 zjkid, 
T1 ZJXM, 
(SELECT floor (MONTHS_BETWEEN (
To_date (to_char (sysdate, 'MM - dd yyyy -'), '- dd yyyy - MM), 
To_date (t1) csny, 'yyyy - MM - dd) 
)/12) 
The from dual) as nl, 
T1 LXSJ, 
T1 GZDW, 
T1 WHCD, 
T1 sxzy, 
T1 cyqssj, 
(select y.z y from zjk_zy y where y.z ylb='0' and y.i d=t1. Zczy) as zczy, 
(select cc. Code_name 
The from unieap up_codelist_code cc, unieap. Up_codelist c 
Where cc. Codelist_id=Arthur c. odelist_id 
And cc. Code=t1. Zczydj 
And Arthur c. ode='ZCDJ' 
And rownum=1) as zczydj, 
(select y.z y from zjk_zy y where y.z ylb='1' and y.i d=t1. Zyfx) as zyfx 
The from v_gs_pszj t1 where 1=1 

) t 
Group by case 
The when t.n l & lt; 30 then 
'30 years old the following (not including 30) 
The when t.n l & gt;=30 and t.n l & lt; 35 then=
'30-35' 
The when t.n l & gt;=36 and t.n l & lt; 40 then=
'36 to 40' 
The when t.n l & gt;=41 and t.n l & lt; 45 then=
'41-45' 
The when t.n l & gt;=46 and t.n l & lt;=50 then 
'46-50' 
The when t.n l & gt; 51 and t.n=l & lt;=55 then 
'51 and 55' 
The when t.n l & gt;=56 and t.n l & lt; 60 then=
'56-60' 
The when t.n l & gt;=61 and t.n l & lt; Then=65 
'61-65 
The when t.n l & gt;=66 and t.n l & lt; Then=69 
'66-69 
The when t.n l & gt; Then=70 
'more than 70 (including 70) 
End 
The order by lx asc

CodePudding user response:

The when t.n l & lt; 30 then
'30 years old the following (not including 30)
The when t.n l & gt;=30 and t.n l & lt; 35 then=
'30-35'
The when t.n l & gt;=36 and t.n l & lt; 40 then=
'36 to 40'
The when t.n l & gt;=41 and t.n l & lt; 45 then=
'41-45'
The when t.n l & gt;=46 and t.n l & lt;=50 then
'46-50'
The when t.n l & gt; 51 and t.n=l & lt;=55 then
'51 and 55'
The when t.n l & gt;=56 and t.n l & lt; 60 then=
'56-60'
The when t.n l & gt;=61 and t.n l & lt; Then=65
'61-65
The when t.n l & gt;=66 and t.n l & lt; Then=69
'66-69
The when t.n l & gt; Then=70
'more than 70 (including 70)

These data, alone on a table, table called t

Query, use left to join

CodePudding user response:

Select sum (case when t.n l & lt; 30 then 1 else 0 end) 30,
The sum (case when t.n l & gt; 30 and t.n l & lt; 40 then 1 else 0=end) 30 to 40,
The sum (case when t.n l & gt; 40 and t.n l & lt;=50 then 1 else 0 end) forty to fifty,
The sum (case when t.n l & gt; Over 50 then 1 else 0 end)
The from
(select trunc (to_char (sysdate, 'yyyymmdd') - to_char (substr (' 341233197612158111 ', 7, 8)))/10000 nl
The from dual t1) t;
This is a line showing all, you need to list the form, can again under the transformation

CodePudding user response:

In advance plus a count

CodePudding user response:

reference 1st floor wmxcn2000 response:

the when t.n l & lt; 30 then
'30 years old the following (not including 30)
The when t.n l & gt;=30 and t.n l & lt; 35 then=
'30-35'
The when t.n l & gt;=36 and t.n l & lt; 40 then=
'36 to 40'
The when t.n l & gt;=41 and t.n l & lt; 45 then=
'41-45'
The when t.n l & gt;=46 and t.n l & lt;=50 then
'46-50'
The when t.n l & gt; 51 and t.n=l & lt;=55 then
'51 and 55'
The when t.n l & gt;=56 and t.n l & lt; 60 then=
'56-60'
The when t.n l & gt;=61 and t.n l & lt; Then=65
'61-65
The when t.n l & gt;=66 and t.n l & lt; Then=69
'66-69
The when t.n l & gt; Then=70
'more than 70 (including 70)

These data, alone on a table, table called t

Query, use left join

CodePudding user response:

Segment (section name, range), use with statement can (if they are fixed, not fixed put table)

 with t as (select 0 qs, zz, 29 '30 years old the following (not including 30)' lx from dual 
Union all select 30 qs, 35 zz, '30-35 from dual 
Union all select 36 qs, 40 zz, '36 to 40 from dual 
Union all select 41 qs, zz, 45 '41-45' from dual 
Union all select 46 qs, zz, 50 '46-50 from dual 
Union all select 51 qs, 55 zz, '51 and 55 from dual 
Union all select 56 qs, 60 zz, '56-60 from dual 
Union all select 61 qs, 65 zz, '61-65 from dual 
Union all select 66 qs, 69 zz, '66-69 from dual 
Union all select 70 qs, 1000 zz, 'more than 70 (including 70) from dual) 
The select t.l x, count (1) the as sumper 
The from t... 
Where.. Nl between t.q s and t.z z 
. 
Group by t.l x;

CodePudding user response:

null