【SQL基础】【牛客网】子查询、联表查询

子查询

基本语法

子查询，也称为嵌套查询，是在另一个 SQL 语句中嵌套的查询。子查询的结果可以被主查询（外部查询）使用，用于满足更复杂的数据检索需求。

例如：

SELECT employee_name
FROM employees
WHERE department_id = (
    SELECT department_id
    FROM departments
    WHERE department_name = 'Sales'
);

这样的查询是找department_name=“Sales"的employee的name, 涉及到多张表，因此需要用子查询建立一张新的表，来和现有的表进行操作。
子查询常用在 WHERE 子句和 FROM 子句后边：当用于 WHERE 子句时，根据不同的运算符，子查询可以返回单行单列、多行单列、单行多列数据。子查询就是要返回能够作为 WHERE 子句查询条件的值。当用于 FROM 子句时，一般返回多行多列数据，相当于返回一张临时表，这样才符合 FROM 后面是表的规则。这种做法能够实现多表联合查询。

相关题目

OrderItems 表

order_num	item_price
a1	10
a2	1
a2	1
a4	2
a5	5
a2	1
a7	7

Orders 表

order_num	cust_id
a1	cust10
a2	cust1
a2	cust1
a4	cust2
a5	cust5
a2	cust1
a7	cust7

SELECT cust_id
FROM Orders
WHERE order_num IN (SELECT DISTINCT order_num
    FROM OrderItems
    where item_price >= 10)

其实就是对于子查询的简单应用。
子查询的难度并不高，只要明白，子查询可以创建出临时的表（单行单列也可以算一张表），而创建出的表是用来辅助查询的即可。

联表查询

基本语法

联表查询往往是建立在两个表存在关系上的。例如a表的外键是b表的主键，而我们需要将两个表的内容综合起来看，才能查询得到想要的内容，这就是联表查询。

例如：

# Employee表：
EmployeeID | Name      | DepartmentID
1          | John      | 2
2          | Jane      | 1
3          | Emily     | 3
# Department表
DepartmentID | DepartmentName
1            | Sales
2            | Engineering
3            | Marketing

如果我们想查询每个员工的所在部门，那只看一个表是肯定做不到的。如果不使用子查询，就还可以使用联表查询，将D表的主键和E表的外键拼接起来，凑成一个新的表（暂时的），在这个新表中我们就可以进行查询。

SELECT E.Name, D.DepartmaentName
FROM Employee
INNER JOIN Department
ON Employees.DepartmentID = Departments.DepartmentID;

不难看出，inner join的操作就是链接的操作，而on则是连接的条件。那么我们可以得到联表查询的基本语法：

SELECT table1.column1, table2.column2...
FROM table1
JOIN table2
ON table1.common_column1 = table2.common_column2;

也可以进行简化的写：

SELECT table1.column1, table2.column2...
FROM table1，table2
WHERE table1.common_column1 = table2.common_column2;

另外，如果两个表的关联字段名相同，也可以用using关键字来进行化简：

SELECT A.id, B.id
FROM A
JOIN B
ON A.id=B.id
# 简化写法
SELECT A.id, B.id
FROM A
JOIN B
USING (id)

注意

联表查询是生成一张暂时的表，在on结束后就消失。因此我们要清楚执行顺序往往是这样的：

1. FROM/JOIN：确定要查询的主表和需要联接的其他表。
2. WHERE：基于指定的条件对FROM子句返回的结果进行筛选。
3. GROUP BY：将筛选后的数据按照一个或多个列进行分组。
4. HAVING：对分组后的结果进行进一步的筛选，使用聚合函数作为条件。
5. SELECT：选择需要返回的列。如果在SELECT子句中使用了聚合函数，这一步也会执行聚合操作。
6. DISTINCT：如果指定了DISTINCT，则在这一步去除重复的行。
7. ORDER BY：根据指定的列对结果进行排序。
   也就是说，SQL 先根据 ON 生成一张临时表，然后再根据 WHERE 对临时表进行筛选。

下图展示了 LEFT JOIN、RIGHT JOIN、INNER JOIN、OUTER JOIN 相关的 7 种用法。

相关题目

（1)

Customers 表

cust_id	cust_name
cust10	andy
cust1	ben
cust2	tony
cust22	tom
cust221	ann
cust2217	hex

Orders 表

order_num	cust_id
a1	cust10
a2	cust1
a3	cust2
a4	cust22
a5	cust221
a7	cust2217

OrderItems 表

order_num	quantity	item_price
a1	10	100
a2	20	10
a3	10	15
a4	25	50
a5	15	25
a7	7	7

【问题】返回 Customers 表中的顾客名称（cust_name）和 Orders 表中的相关订单号（order_num），添加第三列 OrderTotal，其中包含每个订单的总价，并按顾客名称再按订单号对结果进行升序排序。

SELECT c.cust_name, o.order_num, quantity * item_price as OrderTotal
FROM Customers as c, Orders as o, OrderItems as i
where c.cust_id=o.cust_id AND o.order_num = i.order_num
ORDER BY c.cust_name, o.order_num;

这是一种方法，我们用联表查询将三张表建立成一张暂时的表，并且查找对应的内容。
如果我们想用聚合函数SUM，就要这么写：

SELECT c.cust_name, o.order_num, SUM(quantity * item_price) AS OrderTotal
FROM Customers c,Orders o,OrderItems oi
WHERE c.cust_id = o.cust_id AND o.order_num = oi.order_num
GROUP BY c.cust_name, o.order_num
ORDER BY c.cust_name, o.order_num

因为聚合函数是对一组数据进行汇总并返回单一结果，因此如果不加group by去进行分组的话，我们得到的就只有一行：

其实可以简单粗暴的得出一个结论：**如果使用聚合函数，那么我们最好将SELECT的属性都进行分组。**如果你在 SELECT 中使用了聚合函数，但没有对非聚合属性进行分组，SQL引擎将无法确定如何将这些字段与聚合结果结合，这会导致语法错误或者不正确的结果。就像本题中如果不加group by，引擎就不知道如何对聚合的结果分组。

(2)

OrderItems 表

prod_id	order_num
BR01	a0001
BR01	a0002
BR02	a0003
BR02	a0013

Orders 表

order_num	cust_id	order_date
a0001	cust1	2022-01-01 00:00:00
a0002	cust1	2022-01-01 00:01:00
a0003	cust1	2022-01-02 00:00:00
a0013	cust2	2022-01-01 00:20:00

【问题】编写 SQL 语句，使用子查询来确定哪些订单（在 OrderItems 中）购买了 prod_id 为 “BR01” 的产品，然后从 Orders 表中返回对应的顾客 ID（cust_id）和订单日期（order_date），按订购日期对结果进行升序排序

SELECT cust_id, order_date
FROM Orders
WHERE order_num in ( SELECT order_num
		FROM OrderItems
		WHERE prod_id="BR01")
ORDER BY order_date

#或者用联表查询
SELECT cust_id, order_date
FROM Orders as o, OrderItems as oi
WHERE o.order_num=oi.order_num and oi.prod_id="BR01"
ORDER BY order_date;

（3）

OrderItems 表

prod_id	order_num
BR01	a0001
BR01	a0002
BR02	a0003
BR02	a0013

Orders 表

order_num	cust_id	order_date
a0001	cust1	2022-01-01 00:00:00
a0002	cust1	2022-01-01 00:01:00
a0003	cust1	2022-01-02 00:00:00
a0013	cust2	2022-01-01 00:20:00

Customers 表

cust_id	cust_email
cust1	cust1@cust.com
cust2	cust2@cust.com

【问题】返回购买 prod_id 为 BR01 的产品的所有顾客的电子邮件（Customers 表中的 cust_email），结果无需排序。

SELECT cust_mail
FROM OrderItems as oi, Orders as o, Customers as c
WHERE oi.order_num=o.order_num AND c.cust_id=o.cust_id AND prod_id="BR01"

（4）

OrderItems 表

order_num	item_price	quantity
a1	10	105
a2	1	1100
a2	1	200
a4	2	1121
a5	5	10
a2	1	19
a7	7	5

Orders 表

order_num	cust_id
a1	cust10
a2	cust1
a3	cust2
a4	cust22
a5	cust221
a7	cust2217

Customers 表

cust_id	cust_name
cust10	andy
cust1	ben
cust2	tony
cust22	tom
cust221	ann
cust2217	hex

【问题】编写 SQL 语句，返回订单总价不小于 1000 的客户名称和总额（OrderItems 表中的 order_num）。

SELECT cust_name, item_price*quantity as total_price
FROM Customers c,Orders o,OrderItems oi
WHERE c.cust_id = o.cust_id AND oi.order_num = o.order_num AND total_price>=1000
ORDER BY total_price

注意，这是一个错误的示范：上面的文章提到过，select操作的执行顺序是晚于from和where以及join的，因此total_price是没法被找到的，因此会执行出空结果。

SELECT cust_name, oi.total_price
FROM Customers c,Orders o, 
	(SELECT order_num, (item_price * quantity) as total_price
	 FROM OrderItems
	 WHERE item_price * quantity >1000) as oi
WHERE c.cust_id = o.cust_id AND oi.order_num = o.order_num
ORDER BY oi.total_price
# 或者这样
SELECT cust_name, SUM(item_price * quantity) AS total_price
FROM Customers
INNER JOIN Orders USING(cust_id)
INNER JOIN OrderItems USING(order_num)
GROUP BY cust_name
HAVING total_price >= 1000
ORDER BY total_price

这样可以通过子查询先构建出oi这个临时表，临时表中有total_price，再进行联表查询，就会简单。