一道题讲清基本SQL语句

光火
230 阅读4分钟

作者：光火

邮箱：victor_b_zhang@163.com

sql语句是学习与应用数据库所必备的技能，需要我们去重点掌握。本文选取了一道经典sql题目，并为其提供了多种解法，旨在帮助广大入门的读者迅速掌握sql语句的书写方式。

  • 假设，数据库中存在如下两张表：

    • HangarHangar表：记录了机库中所有的飞机种类

      Hangar.PNG

    • PilotSkillsPilotSkills表：记录了每位飞行员的名字，以及他们能够驾驶的飞机

      pilotskills.PNG

    • 我们希望查询会驾驶机库中所有飞机的飞行员。

    • 对于本例而言，应当返回Smith、Wilson 。

通过阅读题干和样例，我们发现每位飞行员能驾驶的飞机是分开存储的，且均为(pilot,plane)(pilot, plane)的形式，因此他们的名字实则被存储了很多次。另外，有些飞行员可能会驾驶额外的飞机，比如Celko能够驾驶的Piper Cub目前就不存在于机库中。

本题的情景并不复杂，想必熟悉关系代数的同学已经发觉，这就是除法运算的常规应用场景。只可惜，这样的扩展操作是没有与之对应的简易sql语句的，不过没有关系，我们提供以下四种方法作为参考。

附一篇讲解关系代数除法运算的文章，内容浅显易懂： 关系代数中的除法运算

方法一

SELECT DISTINCT
	pilot
FROM PilotSkills AS ps1
WHERE NOT EXISTS (
    SELECT
    	*
    FROM Hangar
    EXCEPT
    SELECT 
    	plane 
    FROM PilostSkills AS ps2
    WHERE (ps1.pilot = ps2.pilot)
);

此处，我们利用sqlsql的exceptexcept子句，将机库中的所有飞机，与每一位飞行员的技术栈作差，倘若结果为空，则表明该飞行员ps1会驾驶机库中的全部飞机。由于在PilotSkills表中，每位飞行员可能会占据多行，因此需要在SELECT时加上DISTINCT，只返回不同值。

多说一句，SELECT引导的部分就相当于一个循环，ps1、ps2则类似于指针。在计算内层循环时，外层的ps1已经取定，因此我们可以利用WHERE和ps2找到ps1所指向的那名飞行员，并选出他能驾驶的所有飞机。

因为MySQL不支持EXCEPT子句，所以上述代码无法在MySQL中运行。不过，本文提供的其他三种方法可以适配MySQL，并且能够达成同样的效果。

方法二

SELECT DISTINCT
	pilot
FROM PilotSkills AS ps1
WHERE NOT EXISTS (
    SELECT 
    	*
    FROM Hangar
    WHERE NOT EXISTS (
        SELECT 
        	*
        FROM PilotSkills AS ps2
        WHERE (ps2.pilot = ps1.pilot) 
        	AND (ps2.plane = Hangar.plane) 
    )
);

这种双重NOT EXISTS嵌套的方法非常有意思，但它的思想其实很朴素。我们希望找出可以驾驶机库中所有飞机的飞行员，对于这样的飞行员，机库中不应该存在不在该飞行员技术栈内的飞机。这是一个双重否定句，恰巧就对应了代码中的两个NOT EXISTS。

具体来说，在内层WHERE NOT EXISTS中，我们遍历PilotSkills表，找到ps1指定的飞行员，并且在他的技术栈中寻找对应的飞机Hangar.plane。倘若我们没有找到符合条件的元组，则表明飞行员ps1不会驾驶该飞机，此时SELECT的结果为空，内层NOT EXISTS返回True，外层NOT EXISTS则返回False，最终导致该飞行员被过滤掉。如此，剩余的飞行员，就是我们所要寻找的目标了。

方法三

SELECT DISTINCT
	pilot
FROM PilotSkills AS ps1
WHERE 
(
    SELECT
    	COUNT(DISTINCT plane)
    FROM Hangar
    WHERE plane IN (
        SELECT
        	plane
        FROM PilotSkills AS ps2
        WHERE ps2.pilot = ps1.pilot
    )
)
    = 
(
    SELECT
    	COUNT(plane)
    FROM Hangar
)

方法三和方法四均是借助COUNT完成的，我们数一数机库中有多少种飞机，再数一数飞行员会开其中的多少种，倘若两者相等，则说明该飞行员就是我们寻找的目标。由WHERE引导的子句就是在进行这个比较过程，其中第二个括号包起来的部分是在统计机库中有多少种飞机。

对于第一个括号中的内容，我们还是先固定一个飞行员ps1(外层循环)，然后将他会驾驶的飞机全部列出来，从中选出与Hangar的交集，如此就顺利地过滤了技术栈中额外的飞机。

方法四

SELECT DISTINCT
	pilot
FROM PilotSkills AS ps1
WHERE 
(
    SELECT 
    	COUNT(DISTINCT plane)
    FROM (PilotSkills AS ps2) NATURAL JOIN Hangar
    WHERE ps2.pilot = ps1.pilot
)
	=
(
    SELECT 
    	COUNT(plane)
    FROM Hangar
);

方法四中，我们利用自然连接NATURAL JOIN将PilotSkills和Hangar连接到一起。根据自然连接的特性，此时结果表中剩余的plane均是机库Hangar中有的。此时，我们直接在这张新表中计算ps1占多少行即可。

avatar
文章
阅读
粉丝