作者：小5聊基础
简介：一只喜欢全栈方向的程序员，欢迎咨询，尽绵薄之力答疑解惑
编程原则：Write Less Do More
1、题目内容
题目假设，有一表a，保存的是学生的各个科目成绩
2、查询要求
每门课程都及格的学生名称，实现代码如下
3、考查知识点
group by 字段，having + 内置函数筛选条件过滤
4、模拟数据
可以使用union进行数据集合在一起形成新的表结果集
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
5、查询每一门课程分数都大于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
