可以,但是这个分档固定下来,否则麻烦点 select name,id,trunc(score/10),count(*) from student_score group by name,id,trunc(score/10)
select name,id,trunc(score/10),count(*) from student_score group by name,id,trunc(score/10)上面的语句肯定满足不了楼主的要求。 你都按name,id进行分组了,难道有同一个名字、统一个ID的人得的分数在不同的段中?
select trunc(score/10),count(*) from student_score group by trunc(score/10)
谢谢ghtghtmalone ,我试过了,果然是这样。
select trunc(score/10)*10||'-'||(trunc(score/10)+1)*10,count(*) from student_score group by trunc(score/10)*10||'-'||(trunc(score/10)+1)*10
select sum(case when score > 0 and score <=10 then 1 else 0 end) as a1, sum(case when score > 10 and score <=20 then 1 else 0 end) as a2, sum(case when score > 20 and score <=30 then 1 else 0 end) as a3, sum(case when score > 30 and score <=40 then 1 else 0 end) as a4, sum(case when score > 40 and score <=50 then 1 else 0 end) as a5, sum(case when score > 50 and score <=60 then 1 else 0 end) as a6, sum(case when score > 60 and score <=70 then 1 else 0 end) as a7, sum(case when score > 70 and score <=80 then 1 else 0 end) as a8, sum(case when score > 80 and score <=90 then 1 else 0 end) as a9, sum(case when score > 90 and score <=100 then 1 else 0 end) as a10
from student_score; 你看下我的方法是否符合你的要求!
select trunc(score/10) , count(trunc(score/10)) from student_score group by trunc(score/10) order by 1 这个方法也是可以的,而且很简洁!
select max(name),max(id),trunc(score/10),count(*) from student_score group by trunc(score/10);我感觉是这样,就是说name id必须出现在group by之后.
select trunc(score/10,-1) as score_Group,count(1) from student_score group by trunc(score/10,-1) order by score_Group
select name,id,trunc(score/10),count(*) from student_score
group by name,id,trunc(score/10)
group by name,id,trunc(score/10)上面的语句肯定满足不了楼主的要求。
你都按name,id进行分组了,难道有同一个名字、统一个ID的人得的分数在不同的段中?
group by trunc(score/10)
select trunc(score/10)*10||'-'||(trunc(score/10)+1)*10,count(*) from student_score
group by trunc(score/10)*10||'-'||(trunc(score/10)+1)*10
sum(case when score > 10 and score <=20 then 1 else 0 end) as a2,
sum(case when score > 20 and score <=30 then 1 else 0 end) as a3,
sum(case when score > 30 and score <=40 then 1 else 0 end) as a4,
sum(case when score > 40 and score <=50 then 1 else 0 end) as a5,
sum(case when score > 50 and score <=60 then 1 else 0 end) as a6,
sum(case when score > 60 and score <=70 then 1 else 0 end) as a7,
sum(case when score > 70 and score <=80 then 1 else 0 end) as a8,
sum(case when score > 80 and score <=90 then 1 else 0 end) as a9,
sum(case when score > 90 and score <=100 then 1 else 0 end) as a10
from student_score;
你看下我的方法是否符合你的要求!
from student_score
group by trunc(score/10)
order by 1
这个方法也是可以的,而且很简洁!
group by trunc(score/10);我感觉是这样,就是说name id必须出现在group by之后.
select trunc(score/10,-1) as score_Group,count(1) from student_score
group by trunc(score/10,-1) order by score_Group