SELECT count(case when t.age >= 20 and t.age < 30 then 1 else null end) age20_30, count(case when t.age >= 30 and t.age < 40 then 1 else null end) as age30_40, count(case when t.age >= 40 and t.age < 50 then 1 else null end) as age40_50 from a t
set serveroutput on declare score number(6,2):='&成绩'; begin case when score between 90 and 100 then dbms_output.put_line('优秀'); when score between 80 and 90 then dbms_output.put_line('良好'); when score between 70 and 80 then dbms_output.put_line('中等'); when score between 60 and 70 then dbms_output.put_line('及格'); else dbms_output.put_line('不及格'); end case; end;
谢谢,没有想到SQL语句还有这么复杂的!
select t1.范围 ,count(t1.范围) from (select case when age/10<2 then '0-20' when age/10<3 then '20-30' when age/10<4 then '30-40' when age/10<5 then '40-50' when age/10<6 then '50-60' when age/10<7 then '70-80' when age/10<8 then '80-90' when age/10<9 or age/10=10 then '90-100' end as 范围 from test1) t1 group by t1.范围;
select count(*) from a group by floor(age/10);
不用case when then 不用plsql 能不能实现?
select count(*) as '20-30' ,(select count(*) from a where age between 31 and 40) as '30-40' ,(select count(*) from a where age between 41 and 50) as '41-50' from a where age between 20 and 30
when t.age >= 20 and t.age < 30 then
1
else
null
end) age20_30,
count(case
when t.age >= 30 and t.age < 40 then
1
else
null
end) as age30_40,
count(case
when t.age >= 40 and t.age < 50 then
1
else
null
end) as age40_50
from a t
declare
score number(6,2):='&成绩';
begin
case
when score between 90 and 100 then dbms_output.put_line('优秀');
when score between 80 and 90 then dbms_output.put_line('良好');
when score between 70 and 80 then dbms_output.put_line('中等');
when score between 60 and 70 then dbms_output.put_line('及格'); else
dbms_output.put_line('不及格');
end case;
end;
when age/10<9 or age/10=10 then '90-100' end as 范围 from test1) t1 group by t1.范围;
,(select count(*) from a where age between 31 and 40) as '30-40'
,(select count(*) from a where age between 41 and 50) as '41-50'
from a
where age between 20 and 30