问题简单点:
如:一个表,分数score表,就两个字段,分数scores,姓名name.
查询的结果要求是:分数段 人数
10-20 XX
20-30 XX
30-80 XX
唉,自己想不出来,感觉很简单,对自己无语了
如:一个表,分数score表,就两个字段,分数scores,姓名name.
查询的结果要求是:分数段 人数
10-20 XX
20-30 XX
30-80 XX
唉,自己想不出来,感觉很简单,对自己无语了
解决方案 »
- 请教大家几个Oracle的问题,发图了
- oracle数据库关联查询的sql问题!!!!
- 请教LINUX下C语言用OCI来调用ORACLE存储过程问题.
- 创建ORACLE控制文件副本的问题
- 用C++操作ORACLE的例子
- SqlServer导入Oracle,表名自动加上"",如果改表名?
- 关于数据库恢复的事,求助。
- 【oracle数据库】【Oracle9i视频讲座】
- 请问:用手工的方式建数据库,设置好一系列的环境变量后,换到 c:\svrmgr30时,出现提示:TNS无法与目标相连
- 关于监听的问题?
- powerdesigner生成SQL中的主键constraint和PRIMARY KEY有区别吗
- 用sql*plus 建表时的小问题
类似这样的
SQL> select * from score;
SCORES NAME
---------- ----------
10 1
20 1
21 1
30 1
31 1
40 1
50 1
7 rows selected
SQL>
SQL> select case
2 when scores >=10 and scores <20
3 then '[10,20)'
4 when scores >=20 and scores <30
5 then '[20,30)'
6 else
7 '[30,80)'
8 end ranges,count(*) from score
9 group by case
10 when scores >=10 and scores <20
11 then '[10,20)'
12 when scores >=20 and scores <30
13 then '[20,30)'
14 else
15 '[30,80)'
16 end;
RANGES COUNT(*)
------- ----------
[10,20) 1
[20,30) 2
[30,80) 4
SQL应该是纵向统计20-30 40-50 60-903 55 22===================
这种能搞出来的.
oracle QQ群号:54775466
欢迎这方面的爱好者一起探讨。
from score
group by floor(score/10);
供参考。
select case when scores between 10 and 20 then '10-20'
when scores between 21 and 30 then '21-30'
when scores between 31 and 80 then '31-80'
else '81-100' end, count(*) 人数
from score
group by case when scores between 10 and 20 then '10-20'
when scores between 21 and 30 then '21-30'
when scores between 31 and 80 then '31-80'
else '81-100' end
order by count(*)
SELECT DECODE(FLOOR((55-1)/10),5,'51-60') LEV FROM DUAL
name varchar2(30),
score number(18,2)
)INSERT INTO t(name,score) values('luoyoumou',77);
INSERT INTO t(name,score) values('chenli',17);
INSERT INTO t(name,score) values('liuyang',32);
INSERT INTO t(name,score) values('huajianguo',44);
INSERT INTO t(name,score) values('zhangxu',60);
INSERT INTO t(name,score) values('zhangchunjie',55);
INSERT INTO t(name,score) values('Tom',89);
INSERT INTO t(name,score) values('hanchaoyong',23);
INSERT INTO t(name,score) values('shenxiaofeng',48);
commit;--------------------------------------------------------------
SELECT (case when score>=10 and score<20 then '10-20'
when score>=20 and score<30 then '20-30'
when score>=30 and score<80 then '30-80'
else '' end) as cases, count(1) as "人数"
from t
GROUP BY (case when score>=10 and score<20 then '10-20'
when score>=20 and score<30 then '20-30'
when score>=30 and score<80 then '30-80'
else '' end)
HAVING (case when score>=10 and score<20 then '10-20'
when score>=20 and score<30 then '20-30'
when score>=30 and score<80 then '30-80'
else '' end) IS NOT NULL;
--------------------------------------------------------------
CASES 人数
---------- ----------
30-80 6
10-20 1
20-30 1
when score>=20 and score<30 then '20-30'
when score>=30 and score<80 then '30-80'
else '' end) as cases, count(1) as "人数"
from t
WHERE score>=10 and score<80
GROUP BY (case when score>=10 and score<20 then '10-20'
when score>=20 and score<30 then '20-30'
when score>=30 and score<80 then '30-80'
else '' end);
when score>=20 and score<30 then '20-30'
when score>=30 and score<80 then '30-80'
else '' end) as "分数段", count(1) as "人数"
from t
WHERE score>=10 and score<80
GROUP BY (case when score>=10 and score<20 then '10-20'
when score>=20 and score<30 then '20-30'
when score>=30 and score<80 then '30-80'
else '' end);
(select count(*) from t where scores between(80,89)),
........
from t