请教一道题目

Question

有学生表a
student_id    student_name      score
   001           Anda             78
   002           Bruce            85
   003            Lee             59
   004            David           66
   005           Pieral           91
   006           Sarath           81
   007            Vibhu           48
有成绩分类表blevel          lower          upper
GOOD            91             100
AVERAGE         61             90
BAD             1              60
请教如何统计各个成绩档次上有多少学生以及学生的名字？　　谢谢！

Answer 1

with a as(
select '001' student_id,'Anda' student_name,78 score from dual
union all
select '002' student_id,'Bruce' student_name,85 score from dual
union all
select '003' student_id,'Lee' student_name,59 score from dual
union all
select '004' student_id,'David' student_name,66 score from dual
union all
select '005' student_id,'Pieral' student_name,91 score from dual
union all
select '006' student_id,'Sarath' student_name,81 score from dual
union all
select '007' student_id,'Vibhu' student_name,48 score from dual
),b as(
select 'GOOD' level1,91 lower,100 upper from dual
union all
select 'AVERAGE' level1,61 lower,90 upper from dual
union all
select 'BAD' level1,1 lower,60 upper from dual
)
select * from(
select case when score >= b.lower and score <= b.upper then level1 else null end student_level,student_name from a,b  
) where student_level is not null

Answer 2

顶1楼
也可以把CASE里的判断改下：
score >= b.lower and score <= b.upper  ->
a.score between b.lower and b.upper

Answer 3

前面WITH是构造了你的成绩表和级别表，实际中with应该没有啊

Answer 4

  1  with a as(
  2  select '001' student_id,'Anda' student_name,78 score from dual
  3  union all
  4  select '002' student_id,'Bruce' student_name,85 score from dual
  5  union all
  6  select '003' student_id,'Lee' student_name,59 score from dual
  7  union all
  8  select '004' student_id,'David' student_name,66 score from dual
  9  union all
 10  select '005' student_id,'Pieral' student_name,91 score from dual
 11  union all
 12  select '006' student_id,'Sarath' student_name,81 score from dual
 13  union all
 14  select '007' student_id,'Vibhu' student_name,48 score from dual
 15  ),b as(
 16  select 'GOOD' level1,91 lower,100 upper from dual
 17  union all
 18  select 'AVERAGE' level1,61 lower,90 upper from dual
 19  union all
 20  select 'BAD' level1,1 lower,60 upper from dual
 21  )
 22  select
 23  case when grouping(case when a.score>=lower and a.score<=upper then level1 end)=1
 24  and grouping(student_name)=1 then '总人数'
 25  when  grouping(case when a.score>=lower and a.score<=upper then level1 end)=0
 26  and grouping(student_name)=1 then '该档次小计'
 27  else student_name end student_name,
 28  case when a.score>=lower and a.score<=upper then level1 end level1,
 29  count(1)  汇总
 30  from a,b
 31  where case when a.score>=lower and a.score<=upper then level1 end is not null
 32* group by rollup(case when a.score>=lower and a.score<=upper then level1 end,student_name)
SQL> /STUDENT_NA LEVEL1        汇总
---------- ------- ----------
Lee        BAD              1
Vibhu      BAD              1
该档次小计 BAD              2
Pieral     GOOD             1
该档次小计 GOOD             1
Anda       AVERAGE          1
Bruce      AVERAGE          1
David      AVERAGE          1
Sarath     AVERAGE          1
该档次小计 AVERAGE          4
总人数                      7已选择11行。

Answer 5

谢谢　　但我总觉得，如果我表中有１０００条学生信息，那这样写法岂不是非常大了？？还有，期望结果能是GOOD  1  Pieral
AVERAGE 4 Anda Sarath Bruce David
BAD  2  Lee Vibh这样的格式

Answer 6

select 
    t.level,count(t.student_name),wm_concat(t.student_name) 
from 
   (select bb.level,aa.student_name from a aa,b  bb where aa.score between bb.lower  and  bb.upper) t 
group by t.level;应该可以满足你的要求了。

Answer 7

select 
case when score >= b.lower and score <= b.upper then level1 end level,
count(1) 数量,
replace(wm_concat(student_name),',',' ') student_name
from a,b
group by case when score >= b.lower and score <= b.upper then level1 end level,student_name

Answer 8

 select leveltype,count(*),replace(wm_concat(student_name),',','  ')
 from
  (select leveltype,student_name
 from student s,scoretype st
 where s.score between st.lowerscore and st.upperscore)
 group by leveltypeavg 4 david  anda  bruce  sarath
bad 2 vibhu  lee
good 1 pieral

Answer 9

理解题意：
    学生表a、成绩分类表b；这两个表都是可变的，或者说不固定，分类也可能改动。
解决思路：
1、建立临时表，专门用于存储学生成绩档次表StdtLevel，字段level lower upper Tj    AllStdts
Tj:代表分类统计的数，AllStds代表所有学生名字
2、建立一个存储过程GetStdtLevel过程：
   1）先delete 表StdtLevel
   2)学生成绩游标
            成绩变量=取出游标一条数据学生成绩
            内嵌：成绩分类表游标
                 成绩变量是否在分类游标记录的lower upper
               检查在Allstds中是否存在成绩分类，不存在加入一条，tj=1,AllStds=学生名
               存在，更新对应项 tj=tj+1,AllStds=AllStds+学生名
           结束内嵌游标
       结束学生游标