首页 > 试题广场 >

请写sql语句： 想要了解班级内同学的考试情况，现有一

[问答题]
请写sql语句：
想要了解班级内同学的考试情况，现有一张成绩表表名为A，每行都包含以下内容（已知表中没有重复内容，但所有的考试结果都录入在了同一张表中，一个同学会有多条考试结果）：
student_id，course_name，score
现在需要知道：
1. 每门课程得到成绩的同学人数
2. 每门课程的平均成绩
3. 如果对于每门课程来说，60分以下为不及格，高于60为及格，统计每门课程及格和不及格的人数
查看答案及解析
添加回答
不喝奶茶w头像 不喝奶茶w
1、SELECT course_name,COUNT(score) FROM A
GROUP BY course_name
2、SELECT course_name,AVG(score) FROM A
GROUP BY course_name
3、SELECT course_name,SUM(CASE WHEN score>=60 THEN 1 ELSE 0 END) AS '及格人数',
                     SUM(CASE WHEN score<60 THEN 1 ELSE 0 END) AS '不及格人数 FROM A
GROUP BY course_name
编辑于 2019-08-17 08:53:22 回复(3)
Madison7头像 Madison7
1. select course_name,count(*) from A where score is not null group by course_name;
2 select course_name,avg(score) from A group by course_name;
3 select a.course_name,a.及格,b.不及格 from (select course_name, count(*) as 及格 from A where score >=60 group by course_name ) a inner join (select course_name, count(*) as 不及格 from A where score <60 group by course_name ) b on a.course_name=b.course_name
发表于 2019-08-02 11:10:32 回复(0)
余offer头像 余offer
原数据：

（1）

（2）
（3）

发表于 2019-08-22 22:26:28 回复(0)
欢迎投递字节，内推码SVNMKE2头像 欢迎投递字节，内推码SVNMKE2
1，
select count(distinct student_id) as StuNum
from
    (select   student_id
    from A 
    where student_id not in
       (select distinct student_id
        from A
        where score is null)B
    )C
2,
select course_name,avg(score)
from A
group by course_name
3，
select  course_name,
            count(case when score<60 then student_id end) as notpassNum,
            count(case when score>=60 then student_id end) as passNum
 from A
group by course_name

发表于 2019-07-17 16:58:57 回复(1)
Darcy好运头像 Darcy好运

（1）

Select count(distinct student_id)

From A

Where score != Null or score !=0

 

（2）

Select avg(score)

From A

Group by course_name

 

（3）

Select t1.course_name, t1. 每门课及格人数, t2.每门课不及格人数

From

(

Select course_name, count(distinct student_id) as 每门课及格人数,

From A

Where score >=60

Group by course_name) t1,

(

Select course_name, count(distinct student_id) as 每门课不及格人数,

From A

Where score <60

Group by course_name) t2

Where t1.course_name = t2.course_name

发表于 2019-12-13 14:50:16 回复(0)
Vv201902202104817头像 Vv201902202104817
SELECT A.course_name,count(A.student_id),avg(score),pass,fail
FROM A ,(SELECT course_name,count(student_id) as pass FROM A GROUP BY course_name HAVING score>=60 ) r1,
(SELECT course_name,count(student_id)  as fail FROM A GROUP BY course_name HAVING score<60 ) r2
WHERE A.course_name=r1.course_name AND A.course_name=r2.course_name and r1.course_name=r2.course_name
GROUP BY A.course_name
发表于 2019-05-08 22:32:28 回复(3)
Akira30头像 Akira30

select 

course_name,

count(score) as stu_cnt, 

avg(score) as avg_score,

count( if(score<60,1,null)) '不及格',

count(if(score>=60,1,null)) '及格'

from A 

group by course_name

发表于 2023-03-23 15:16:52 回复(0)
2htttttttttt头像 2htttttttttt
1.
select distinct course_name,count(*)
from A
group by course_name

2.
select distinct course_name,AVG(score)
from A
group by course_name

3.
select distinct course_name,
     sum (case when score >= 60 then 1 else 0 end ) as 及格人数，
     sum (case when score < 60 then 1 else 0 end ) as 不及格人数
from A
group by course_name
发表于 2021-09-03 10:10:49 回复(0)
offer快带碗里来头像 offer快带碗里来
select 
course_name,count (distinct student_id) as student_num, avg(score) as avg_score
from A

select 
course _name,
case when score <60,then '不及格'
else '及格‘
end
count(student_id) as student_num
from A
group by1,2
发表于 2021-08-20 14:58:00 回复(0)
牛牛鱼头像 牛牛鱼
1.
select count(student_id) cnt 
from A 
group by course_name 

2.
select avg(score) 
from A 
group by course_name 

3.
select count(rank = ‘及格’) cnt1, count(rank = ‘不及格’) cnt2 
from (select (case when score<60 then ‘不及格’ else ‘及格’ end ) rank, student_id, course_name, score from A ) t 
group by course_name 
发表于 2021-03-30 11:01:03 回复(0)
justdoit0704215401头像 justdoit0704215401 
select 
    COUNT(student_id),
    avg(score),
    course_name
from A 
GROUP BY course_name

3.select
        case when score >=60 then 1 when score<60 then 0 else "其他" end as is_pass
        COUNT(student_id),
        course_name
from A 
GROUP BY  course_name,case when score >=60 then 1 when score<60 then 0 else "其他" end 

发表于 2020-08-20 00:10:04 回复(0)
Breeze-Yzy头像 Breeze-Yzy
1.select  count( distinct student_id) from A 
2.select  avg(score) from A goup by course_name
3.  (select course_name , count(distinct student_id)  from A where score >= 60  as B) join (select course_name, count(distinct student_id) from A where score <60  as C )on B.course_name=C.course_name
发表于 2020-07-29 15:24:32 回复(0)
偷偷打工头像 偷偷打工
1.
select  course_name, count(distinct  student_id)  as  人数  from  A  group  by  course_name;
2.
select   course_name, avg(score)  over(partition  by  course_name order by score)  as 平均成绩  from  A;
3.
select  course_name,
count(distinct  case  when  score < 60 then student_id  end)  as 不及格人数,
count(distinct  case  when  score >= 60 then student_id  end)  as 不及格人数
from A  group by  course_name;
发表于 2020-07-20 16:42:23 回复(0)
努力找到工作，保持积极乐观头像 努力找到工作，保持积极乐观
1.SELECT course_name,count(score) FROM A GROUP BY course_name
2. SELECT course_name ,AVG(score) AS avg_score FROM A GROUP BY course_name
3.SELECT course_name,SUM(case WHEN score>=60 THEN 1ELSE 0 END) AS '及格人数'，
SUM(case WHEN score<60 THEN 1ELSE 0 END) AS '不及格人数' FROM A 
GROUP BY course_name
发表于 2020-05-31 17:50:59 回复(0)
牛客824986767号头像 牛客824986767号
1.select count(distinct student_id) from (select student_id,count(score),count(course_name) from A group by student_id having count(score)=count(course_name)) ;
2.select course_name,sum(score)/count(score) from A group by course_name
3.select course_name,sum(case when score<60 then 1 else 0 end) as '不及格人数',sum(case when score>=60 then 1 else 0 end) as '及格人数' from A group by course_name
发表于 2020-05-05 16:18:16 回复(0)
。。。201811061535863头像 。。。201811061535863
1.每门课程得到成绩的同学人数 
SELECT  course_name,COUNT(*) AS cnt
FROM A
GROUP BY course_name;
2. 每门课程的平均成绩
SELECT course_name,mean(score) as mean
FROM A
GROUP BY course_name;
3. 如果对于每门课程来说，60分以下为不及格，高于60为及格，统计每门课程及格和不及格的人数 
SELECT  course_name, 
count(case when score <60 then score else null end ) as bujige,
count(case when score>=60 then score else null end) as jige
FROM A
GROUP BY course_name;
发表于 2020-02-12 20:49:43 回复(0)
颖子颖子小颖子头像 颖子颖子小颖子
1.&2.
select course_name, count(student_id),average(score) 
from A
group by course_name;

3.
select course_name, count(case when score<60 then student_id end), count(case when score>=60 then student_id end)
from A
group by course_name;
发表于 2020-02-10 12:37:57 回复(0)
王尔德他舅头像 王尔德他舅
select course_name
           ,a.score_type
           ,count(student_id)
(select case when score>=60 then ‘及格'
                   when score<60 then '不及格'
                    end as score_type
from A)a
group by course_name
               ,a.score_type;   
发表于 2019-11-05 22:16:26 回复(0)
Milk.L.头像 Milk.L.
1、select count(*) from (select sum(score) as score from A group by student_id having score > 0);
2、select course_name,avg(score) from A group by course_name;
3、(select course_name,count(*) as pass from A group by course_name having score >= 60) union (select course_name,count(*) as fail from A group by course_name having score < 60);
发表于 2019-10-03 20:56:35 回复(0)
求一个oc！头像 求一个oc！
select course_name, count(distinct student_id) from A
where score is not null
group by course_name

select course_name, avg(score) from A
where score is not null
group by course_name

select course_name
           ,case when score >= 60 then '及格' when score < 60 then '不及格' end as grade
           ,count(distinct student_id)
from A
group by course_name,grade

发表于 2019-09-06 21:03:48 回复(0)
提交观点

问题信息

C++工程师 运维工程师 2019 小红书 数据分析师 Java工程师
上传者：小小
难度：
26条回答 2173浏览

热门推荐

相关试题

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

扫描二维码，关注牛客公众号