sql一个题的解法分析讲解
2024-10-19 02:27:20
本篇讲述的是对一个sql面试题的细致语法讲解。关于执行流程（on where），内连接，外连接（左右）上实用。关于这些基本的语法知识请参考我前面的sql基本语法。
S(SNO,SNAME)学生学号，姓名
C（CNO,CNAME,CTEACHER）课程号，课程名，课成老师名。
SC(SNO,CNO,SCGRADE)，SNO学号，CNO课程号，SCGRADE成绩。
题1
要求：列出“1”号课程成绩比“2”号课程成绩高的所有学生学号及其“1”号课程和“2”号课程的成绩
要求：列出“1”号课程成绩比“2”号课程成绩高的所有学生学号及其“1”号课程和“2”号课程的成绩
1，分别查出1 2号课程的所有列表（包括学号，课程，成绩）。
2，条件1课程表的成绩>2课程表的成绩。两表连接查询。
3，隐士的条件，这两个表中的学号相等。两表连接查询。
4，select 表1或表2的序号，表1.成绩，表2.成绩。
5，分别起别名。
SELECT A.SNO as '学号',A.SCGRADE AS '1号课程',b.SCGRADE as '2号课程' FROM
(SELECT *FROM SC WHERE CNO=1)AS A CROSS JOIN (SELECT * FROM SC WHERE CNO=2)as b
WHERE A.SCGRADE>b.SCGRADE AND A.SNO=b.SNO; SELECT A.SNO as '学号',A.SCGRADE AS '1号课程',b.SCGRADE as '2号课程' FROM
(SELECT SNO,SCGRADE FROM SC WHERE CNO=1)AS A CROSS JOIN (SELECT SNO,SCGRADE FROM SC WHERE CNO=2)as b
WHERE A.SCGRADE>b.SCGRADE AND A.SNO=b.SNO; SELECT A.SNO as '学号',A.SCGRADE AS '1号课程',b.SCGRADE as '2号课程' FROM
(SELECT SNO,SCGRADE FROM SC WHERE CNO=1)AS A INNER JOIN (SELECT SNO,SCGRADE FROM SC WHERE CNO=2)as b
ON A.SCGRADE>b.SCGRADE AND A.SNO=b.SNO; SELECT A.SNO as '学号',A.SCGRADE AS '1号课程',b.SCGRADE as '2号课程' FROM
(SELECT SNO,SCGRADE FROM SC WHERE CNO=1)AS A INNER JOIN (SELECT SNO,SCGRADE FROM SC WHERE CNO=2)as b
ON A.SCGRADE>b.SCGRADE WHERE A.SNO=b.SNO; SELECT A.SNO as '学号',A.SCGRADE AS '1号课程',b.SCGRADE as '2号课程' FROM
(SELECT SNO,SCGRADE FROM SC WHERE CNO=1)AS A LEFT JOIN (SELECT SNO,SCGRADE FROM SC WHERE CNO=2)as b
ON A.SCGRADE>b.SCGRADE AND A.SNO=b.SNO; SELECT A.SNO as '学号',A.SCGRADE AS '1号课程',b.SCGRADE as '2号课程' FROM
(SELECT SNO,SCGRADE FROM SC WHERE CNO=1)AS A LEFT JOIN (SELECT SNO,SCGRADE FROM SC WHERE CNO=2)as b
ON A.SCGRADE>b.SCGRADE WHERE A.SNO=b.SNO; SELECT A.SNO as '学号',A.SCGRADE AS '1号课程',b.SCGRADE as '2号课程' FROM
(SELECT SNO,SCGRADE FROM SC WHERE CNO=1)AS A RIGHT JOIN (SELECT SNO,SCGRADE FROM SC WHERE CNO=2)as b
ON A.SCGRADE>b.SCGRADE AND A.SNO=b.SNO;
所有方法
第一种：
SELECT A.SNO as '学号',A.SCGRADE AS '1号课程',b.SCGRADE as '2号课程' FROM
(SELECT *FROM SC WHERE CNO=1)AS A CROSS JOIN (SELECT * FROM SC WHERE CNO=2)as b
WHERE A.SCGRADE>b.SCGRADE AND A.SNO=b.SNO;
第二种：
SELECT A.SNO as '学号',A.SCGRADE AS '1号课程',b.SCGRADE as '2号课程' FROM
(SELECT SNO,SCGRADE FROM SC WHERE CNO=1)AS A CROSS JOIN (SELECT SNO,SCGRADE FROM SC WHERE CNO=2)as b
WHERE A.SCGRADE>b.SCGRADE AND A.SNO=b.SNO;
第三种
SELECT A.SNO as '学号',A.SCGRADE AS '1号课程',b.SCGRADE as '2号课程' FROM
(SELECT SNO,SCGRADE FROM SC WHERE CNO=1)AS A INNER JOIN (SELECT SNO,SCGRADE FROM SC WHERE CNO=2)as b
ON A.SCGRADE>b.SCGRADE AND A.SNO=b.SNO;
第四种：
SELECT A.SNO as '学号',A.SCGRADE AS '1号课程',b.SCGRADE as '2号课程' FROM
(SELECT SNO,SCGRADE FROM SC WHERE CNO=1)AS A INNER JOIN (SELECT SNO,SCGRADE FROM SC WHERE CNO=2)as b
ON A.SCGRADE>b.SCGRADE WHERE A.SNO=b.SNO;
第五种：
SELECT A.SNO as '学号',A.SCGRADE AS '1号课程',b.SCGRADE as '2号课程' FROM
(SELECT SNO,SCGRADE FROM SC WHERE CNO=1)AS A LEFT JOIN (SELECT SNO,SCGRADE FROM SC WHERE CNO=2)as b
ON A.SCGRADE>b.SCGRADE AND A.SNO=b.SNO;
第六种：
SELECT A.SNO as '学号',A.SCGRADE AS '1号课程',b.SCGRADE as '2号课程' FROM
(SELECT SNO,SCGRADE FROM SC WHERE CNO=1)AS A LEFT JOIN (SELECT SNO,SCGRADE FROM SC WHERE CNO=2)as b
ON A.SCGRADE>b.SCGRADE WHERE A.SNO=b.SNO;
第七种：
SELECT A.SNO as '学号',A.SCGRADE AS '1号课程',b.SCGRADE as '2号课程' FROM
(SELECT SNO,SCGRADE FROM SC WHERE CNO=1)AS A RIGHT JOIN (SELECT SNO,SCGRADE FROM SC WHERE CNO=2)as b
ON A.SCGRADE>b.SCGRADE AND A.SNO=b.SNO;
方法1和2之间区别是两个连接表的查询字段的多余与否。
方法3演示了inner jion和on的连接使用，并和cross jion的区别。
方法4和3演示了内连接流程顺序，先from内（包含on）走完，再where。内连接和on where的功能相同，但顺序不同。
方法5演示纯的左连接和on多条件使用。
方法6和5演示了左链接流程顺序，先from内（包含on）走完，再where。内连接和on where的功能相同，但顺序不同。
方法7右连接的on多条件使用。
题2
找出没有选修过“老师1”的课程的所有学生姓名。
SELECT S.SNAME FROM S WHERE SNAME NOT in(
SELECT DISTINCT a.SNAME
FROM S AS a INNER JOIN SC AS b
ON a.SNO=b.SNO
WHERE b.CNO =(SELECT CNO FROM C WHERE C.CTEACHER='老师1'));
代码
SELECT DISTINCT a.SNAME--*
FROM S AS a INNER JOIN SC AS b
ON a.SNO=b.SNO
WHERE b.CNO IN(SELECT CNO FROM C WHERE C.CTEACHER!='老师1')
错误代码
题3
列出2门或者2门以上不及格课程的学生姓名及其平均成绩。
SELECT a.SNAME ,avg(b.SCGRADE)
from
S AS a
INNER JOIN SC AS b
ON a.SNO=b.SNO WHERE a.SNO IN
(SELECT SC.SNO FROM SC WHERE SC.SCGRADE<60 GROUP BY SC.SNO HAVING count(*)>=2)
GROUP BY a.SNAME;
正确代码
SELECT a.SNAME ,avg(p)
from
S AS a
INNER JOIN
(SELECT SC.SNO,avg(SCGRADE) AS p FROM SC WHERE SC.SCGRADE<60 GROUP BY SC.SNO HAVING count(*)>=2)AS b
ON a.SNO=b.SNO;
错误代码
1，
题4
列举即学过“1”号课程，有学过“2”号课程的所有学生学号
SELECT DISTINCT SC.SNO
FROM SC WHERE SC.SNO IN
((SELECT SNO FROM SC WHERE CNO=)INTERSECT (SELECT SNO FROM SC WHERE CNO=) )
表联合
题5
一张表biao，id cash。要求不用max和order by 查出最cash值最大的是多少？
SELECT id "编号",cash "最高工资" FROM Salary WHERE cash NOT IN
(SELECT DISTINCT a.cash FROM Salary a,Salary b WHERE a.cash<b.cash);
先用笛卡尔积求出小于最大数的所有值。再not in 主要自己理解。求最小值：把小于号换成大于号
题6
一张表biao，id name age cash。里面1000万以上的大数据，请分别查出年龄在20-30，cash2000-3000 年龄 30-40 cash3000-5000,年龄40-50，cash5000以上的人名。
SELECT name,
CASE WHEN (age>=20 and age<=30 and cashM>=2000 and cashM<=10000) then ''
WHEN (age>=30 and age<=80 and cashM>=3000 and cashM<=10000) then ''
END AS "年龄工资"
from cash
case when 多条件then显示end 对一新列进行操作。as起个列名
这些区别已经区分非常清楚。具体语法请看我前几篇关于数据库的基本语法文章。
当然，这些不是什么前沿技术，但很细致，对不对？
最新文章
- Nginx如何处理一个请求
- powershell玩转SQL SERVER所有版本
- Android IOS WebRTC 音视频开发总结（七五）-- WebRTC视频通信中的错误恢复机制
- 一种读取Exchange的用户未读邮件数方法！
- 《挑战程序设计竞赛》 4.1.1 矩阵 P286
- 找不到请求的 .Net Framework Data Provider。可能没有安装。
- hibou 主界面自己侧滑的定义
- 基于Stm32的MP3播放器设计与实现
- ZigZag Conversion2015年6月23日
- 浏览器抓包（post）
- 炫酷线条动画--svg
- 极光征文 | 写写文章就能赢 Filco，岂不美滋滋
- linux_shell 编程学习-初识she'll
- 学号 20175329 2018-2019-3《Java程序设计》第八周学习总结
- Javascript高级编程学习笔记（52）—— DOM2和DOM3（4）元素大小
- WPF仿网易云音乐系列（序）
- UPC 6616 Small Mulitple
- Java常用工具类之ArrayUtil
- Hadoop2.7.4 在 Windows 10(64位) 详细配置
- 在Ubuntu上安装pyenv 相关问题Common build problems
热门文章
- thinkPHP5 引入模板
- IPv6调用java后端接口报错：java.net.SocketException: Protocol family unavailable
- PHP版本VC6与VC9/VC11/VC14、Thread Safe与None-Thread Safe等的区别
- select选中事件
- Linux 后台进程管理
- 一个关于考勤统计的sql研究
- PHP 序列化变量的 4 种方法
- javascript的密封对象之seal(),isSealed()方法
- kudu架构(转)
- 关于sencha touch在华为、红米等部分手机下hide事件失效，msgbox无法关闭的解决方案(已更新最新解决方案)