【小5】sql server基础面试题解答之查询每门课程都及格的学生名称_sql查询出所选课程全部都及格的学生姓名?

1、题目假设，有一表a，保存的是学生的各个科目成绩

2、查询要求，每门课程都及格的学生名称，实现代码如下

考查知识点：group by 字段，having + 内置函数筛选条件过滤

select name,min(convert(int,fenshu)) as fenshu from (
select '张三' as name,'语文' as kecheng,'81' as fenshu
union
select '张三' as name,'数学' as kecheng,'75' as fenshu
union
select '李四' as name,'语文' as kecheng,'76' as fenshu
union
select '李四' as name,'数学' as kecheng,'90' as fenshu
union
select '王五' as name,'语文' as kecheng,'81' as fenshu
union
select '王五' as name,'数学' as kecheng,'100' as fenshu
union
select '王五' as name,'英语' as kecheng,'90' as fenshu
) as a group by name
having min(convert(int,fenshu))>80

3、同样原理，查询每门课都大于80分的学生

--用一条语句，查询出每门课都大于80的学生姓名（反向逆推：课程中分数最低大于80，则也是每门课都大于80）
--用户、每门功课
SELECT t.name,MIN(CONVERT(INT,t.fenshu)) fenshu FROM (
SELECT '张三' AS name,'语文' AS kecheng,'81' AS fenshu
UNION ALL
SELECT '张三' AS name,'数学' AS kecheng,'75' AS fenshu
UNION ALL
SELECT '李四' AS name,'语文' AS kecheng,'76' AS fenshu
UNION ALL
SELECT '李四' AS name,'数学' AS kecheng,'90' AS fenshu
UNION ALL
SELECT '王五' AS name,'语文' AS kecheng,'81' AS fenshu
UNION ALL
SELECT '王五' AS name,'数学' AS kecheng,'100' AS fenshu
UNION ALL
SELECT '王五' AS name,'英语' AS kecheng,'90' AS fenshu
--UNION ALL
--SELECT '王五' AS name,'体育' AS kecheng,'79' AS fenshu
) AS t GROUP BY t.name HAVING MIN(CONVERT(INT,t.fenshu))>80