sql一个题的解法分析讲解
本篇讲述的是对一个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,分别起别名。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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”的课程的所有学生姓名。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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'));
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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门以上不及格课程的学生姓名及其平均成绩。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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;
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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”号课程的所有学生学号
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SELECT DISTINCT SC.SNO FROM SC WHERE SC.SNO IN ((SELECT SNO FROM SC WHERE CNO=1)INTERSECT (SELECT SNO FROM SC WHERE CNO=2) )
题5
一张表biao,id cash。要求不用max和order by 查出最cash值最大的是多少?
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 SELECT id "编号",cash "最高工资" FROM Salary WHERE cash NOT IN 2 (SELECT DISTINCT a.cash FROM Salary a,Salary b WHERE a.cash<b.cash);
题6
一张表biao,id name age cash。里面1000万以上的大数据,请分别查出年龄在20-30,cash2000-3000 年龄 30-40 cash3000-5000,年龄40-50,cash5000以上的人名。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SELECT name, CASE WHEN (age>=20 and age<=30 and cashM>=2000 and cashM<=10000) then '1' WHEN (age>=30 and age<=80 and cashM>=3000 and cashM<=10000) then '2' END AS "年龄工资" from cash
这些区别已经区分非常清楚。具体语法请看我前几篇关于数据库的基本语法文章。
当然,这些不是什么前沿技术,但很细致,对不对?