我正在参加「掘金·启航计划」
在本篇文章中，主要讲讲运用数据库基础知识点进行查询
在实际项目种，如果有接触到学生相关的一些需求，那么查询学生课程以及成绩的场景会比较多
- 主要知识点列表 | 编号 | 语言或插件 | 知识点 | 说明 | | --- | --- | --- | --- | | 1 | sql server | union | 连接 | | 2 | sql server | group by ... having | 分组 | | 3 | sql server | min() | 获取最小值 | | 4 | sql server | convert(数据类型，字段) | 数据类型转换 |
【知识点】
1、union 可以将A记录和B记录集合在一个数据集里，变成AB两条记录，必须要保持A记录和B记录字段名和字段数相同
【查询要求如下】
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
