用sql如何查出各科成绩低于各科平均分的学生，并显示低于平均分的科目和成绩，学生姓名？

答案:3 悬赏:0 手机版

解决时间 2021-07-18 11:14

提问者网友：记得曾经
2021-07-18 08:05

最佳答案

五星知识达人网友：鸠书
2021-07-18 09:45

select *

from 表名

where 英语<(select avg(英语) from 表名)

union

select *

from 表名

where JSP<(select avg(JSP) from 表名)

union

select *

from 表名

where CPP<=(select avg(CPP) from 表名)

union

select*from 表名

where Java<(select avg(Java) from 表名)

union

select *from 表名

where Access<(select avg(Access) from 表名)

全部回答

1楼网友：轻熟杀无赦
2021-07-18 10:57

select 学号,姓名,英语,''

from s

where 英语<=(select avg(英语) from 表名)

union

select 学号,姓名,'' as '英语'

from s

where JSP<=(select avg(JSP) from 表名)

union

select 学号,姓名,'' as '英语'

from s

where CPP<=(select avg(CPP) from 表名)

union

select 学号,姓名,'' as '英语'

from s

where Java<=(select avg(Java) from 表名)

union

select 学号,姓名,'' as '英语'

from s

where Access<=(select avg(Access) from 表名)

2楼网友：躲不过心动
2021-07-18 10:05

select 学号,姓名,英语,'' AS 'JSP','' AS 'CPP','' AS 'Java','' AS 'Access'

from 表名

where 英语<(select avg(英语) from 表名)

union

select 学号,姓名,'' as '英语',JSP,'' AS 'CPP','' AS 'Java','' AS 'Access'

from 表名

where JSP<(select avg(JSP) from 表名)

union

select 学号,姓名,'' as '英语','' as 'JSP',CPP,'' AS 'Java','' AS 'Access'

from 表名

where CPP<(select avg(CPP) from 表名)

union

select 学号,姓名,'' as '英语','' as 'JSP','' as 'CPP',Java,'' AS 'Access'

from 表名

where Java<(select avg(Java) from 表名)

union

select 学号,姓名,'' as '英语','' as 'JSP','' as 'CPP','' AS 'Java',Access

from 表名

where Access<(select avg(Access) from 表名)

这表设计的。。。还不如在Excel里面做呢。。。

我要举报

如以上回答内容为低俗、色情、不良、暴力、侵权、涉及违法等信息，可以点下面链接进行举报！

点此我要举报以上问答信息