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，分别查出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=1)INTERSECT (SELECT SNO FROM SC WHERE CNO=2) )

表联合

题5

一张表biao，id cash。要求不用max和order by 查出最cash值最大的是多少？

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);

先用笛卡尔积求出小于最大数的所有值。再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 '1'
 WHEN (age>=30 and age<=80 and cashM>=3000 and cashM<=10000) then '2'
END AS "年龄工资" 
from cash

case when 多条件then显示end 对一新列进行操作。as起个列名

这些区别已经区分非常清楚。具体语法请看我前几篇关于数据库的基本语法文章。

当然，这些不是什么前沿技术，但很细致，对不对？