请教一个SQL语句

Question

如过表内有两个字段，课程号sc_courseidhe 成绩sc_score,如何能查出每个有成绩低于60分的课程号，所对应的课程的平均分。
帮帮忙，新手。

Answer 1

两种方法随便选一种吧,试试看~~~
select *
  from (
        select distinct
               tt.sc_courseid,
               decode(sign(min(tt.sc_score) over(partition by tt.sc_courseid)-60),1,'xf',avg(tt.sc_score) over(partition by tt.sc_courseid)) as avg_score
          from tablename tt
       )zz
 where zz.avg_score <> 'xf';select tt2.sc_courseid,
       avg(tt2.sc_score)
  from tablename tt2
 where tt2.sc_courseid in (
                           select tt.sc_courseid
                             from tablename tt
                            group by tt.sc_courseid
                           having min(tt.sc_score) < 60
                          )
 group by tt2.sc_courseid;
 

Answer 2

select tt2.sc_courseid,
       avg(tt2.sc_score)
  from tablename tt2
 where tt2.sc_courseid in (
                           select tt.sc_courseid
                             from tablename tt
                            group by tt.sc_courseid
                           having min(tt.sc_score) < 60
                          )
 group by tt2.sc_courseid;

Answer 3

SELECT sc_courseidhe, Avg(sc_score)
FROM table1
GROUP BY sc_courseidhe
HAVING Min(sc_score)<=60

Answer 4

SELECT sc_courseidhe, Avg(sc_score) FROM table1 GROUP BY sc_courseidhe HAVING Min(sc_score)<=60