首页 新闻 赞助 找找看

求有关统计的sql语句

0
悬赏园豆:10 [已解决问题] 解决于 2011-11-03 10:36

有一张表记录学生信息,包括id,名字,年龄:student(id,name,age)。

现要统计如下信息用一条sql语句实现:统计年龄在10-20的人数,20-30的人数,其他年龄人数,总人数。

请教一条sql语句统计出不同年龄段人数和总人数的写法。

问题补充:

sql语句中用到case when 

MySparrow的主页 MySparrow | 菜鸟二级 | 园豆:263
提问于:2011-11-02 20:59
< >
分享
最佳答案
1

横向的结果好像只能用子查询,纵向的要用union all

横向:

select 
(select count(*) from student where age between 10 and 20) as a,
(select count(*) from studen where age between 20 and 30) as b,
.................

 

纵向:

select '10-20',count(*) from student where age between 10 and 20
union all
select '20-30',count(*) from student where age between 20 and 30
................
收获园豆:5
丁学 | 专家六级 |园豆:18730 | 2011-11-02 21:33
其他回答(3)
0
View Code
1 select a,count(1) from (SELECT (CASE  
2 WHEN AGE BETWEEN 10 AND 20 THEN 1
3 WHEN AGE BETWEEN 10 AND 20 THEN 2
4 ELSE 3 END ) AS A
5 FROM student) AS T group by A

没有环境随手写的,你试一试

收获园豆:5
小小刀 | 园豆:1991 (小虾三级) | 2011-11-02 23:13
0

1楼正解

喬喬AI | 园豆:996 (小虾三级) | 2011-11-03 09:54
0

Select sum(case when age between 10 and 20 then 1 else 0 end) as 学生人数,

          sum(case when age between 20 and 30 then 1 else 0 end) as 青年人数,

          sum(case when (age >30 and age<10) then 1 else 0 end) as 其他人数

From Student  With (nolock)

Where 1=1.....

猫头鹰静静 | 园豆:205 (菜鸟二级) | 2012-02-05 19:54
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册