select case when age<30 then '<30'
when age>=30 and age <40 then '30-40'
when age>=40 and age <50 then '40-50'
else '>50' end ,
count(*)
from age
group by case when age<30 then '<30'
when age>=30 and age <40 then '30-40'
when age>=40 and age <50 then '40-50'
else '>50' end
可以老..
你的年龄一定是去整的吗?
用这个SQl简单很多
select a,count(a)
from
(select (case when age<30 then '<30'
when age>=30 and age <40 then '30-40'
when age>=40 and age <50 then '40-50'
else '>50') a
from age)
group by a
SQL> create table t2 (
2 id number,
3 name char(10),
4 age number);
表已创建。
SQL> begin
2 for i in 1.. 100 loop
3 insert into t2 values(i,'test'||to_char(i),20+i);
4 commit;
5 end loop;
6 end;
7 /
PL/SQL 过程已成功完成。
SQL> select decode(sign(age-50),-1,'小于50岁',0,'等于50岁',decode(sign(age-68),-
1,'小于68岁',0,'等于68岁','大于68岁')),
2 count(1) from t2 group by rollup(decode(sign(age-50),-1,'小于50岁',0,'等于5
0岁',decode(sign(age-68),-1,'小于68岁',0,'等于68岁','大于68岁')))
3 /
和Nlg521方法相同;
但是觉得Nlg521的sql有些问题:子分组查询中要使用count函数,否则相同的age将计算不准确.
Select
Case When a=1 Then '三十以下'
When a=2 Then '小于40'
When a=3 Then '小于50'
When a=4 Then '大于等于50' End ,
sum(b)
From (
Select Case When age<30 Then '1'
When age Between 30 And 39 Then '2'
When age Between 40 And 49 Then '3'
When age>=50 Then '4' End a ,
Count(age) b
From t_emp Group By age
) Group By a;
这样的写法很复杂~,希望高手能更好的方法
SELECT
CASE f_Count
WHEN 1 THEN COUNT(*)
WHEN 2 THEN COUNT(*)
WHEN 3 THEN COUNT(*)
WHEN 4 THEN COUNT(*)
END AS f_UserCount ,
CASE f_Count
WHEN 1 THEN '三十以下'
WHEN 2 THEN '小于三十'
WHEN 3 THEN '小于四十'
WHEN 4 THEN '大于五十'
END AS f_TotalName
FROM
(
SELECT
CASE
WHEN f_Age < 30 THEN 1
WHEN f_Age >= 30 AND f_Age < 40 THEN 2
WHEN f_Age >= 40 AND f_Age < 50 THEN 3
WHEN f_Age >= 50 THEN 4
END AS f_Count
FROM t_Test GROUP BY f_Age
)
GROUP BY f_Count