用sql如何查出各科成绩低于各科平均分的学生，并显示低于平均分的科目和成绩，学生姓名？
- 提问者网友：记得曾经
- 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里面做呢。。。