MySQL非会员简单题集 21.11.08

---

175.组合两个表

SELECT FirstName, LastName, City, State 
From Person LEFT OUTER JOIN Address
ON Person.PersonId = Address.PersonId;

《MySQL必知必会》第16章 创建高级联结 相关练习

1. customers表 和 orders表

2. 分别用内部联结和外部联结通过cust_id联结两张表

• 内部联结

SELECT customers.cust_id, orders.order_num 
FROM customers INNER JOIN orders
ON customers.cust_id = orders.cust_id;

• 外部联结

SELECT customers.cust_id, orders.order_num
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id;

由此可见，内部联结是对cust_id取交集。外部联结是对cust_id取并集，在customers表中存在的cust_id，在orders表中不存在，那么也就没有对应的order_num(因为order_num来自orders)，因此合并时用NULL表示。

回到175.合并两张表，题目要求无论person是否有地址信息，都需要提供City和State，由此可知，部分PersonId可能不在Address表中出现，因此需要用外部联结。

知识点：

1. 内部联结 INNER ON
2. 外部联结 OUTER ON(有时需要加LEFT或RIGHT)

---

181.超过经理收入的员工

SELECT E1.Name AS Employee
FROM Employee AS E1, Employee AS E2
WHERE E1.ManagerId = E2.Id 
	AND E1.Salary > E2.Salary;

《MySQL必知必会》第16章 创建高级联结 自联结

官方题解二：

SELECT E1.Name AS Employee
FROM Employee AS E1 JOIN Employee AS E2
	 ON E1.ManagerId = E2.Id
	 AND E1.Salary > E2.Salary;

知识点：

1. 自联结
2. JOIN … ON …

---

182.查找重复电子邮箱

1. 我的解法

SELECT DISTINCT P1.Email 
FROM Person AS P1, Person AS P2
WHERE P1.Id != P2.ID 
    AND P1.Email = P2.Email;

思路：因为是在一个表里，所以当id不同而Email相同时，说明该邮箱重复，用自联结。又因为只返回一个重复的Email，所以加了限定词DISTINCT。

2. 官方解法一：使用 GROUP BY 和 临时表

   • 首先计算每个Email出现的个数（需要用到GROUP BY），存在临时表中，这样做的原因是可以让Email只出现一次。

   • 然后从产生的临时表中提取num值大于1的Email列。

   • 注意：如果不给临时表命名会报Every derived table must have its own alias异常。

SELECT Email, COUNT(Eamil) AS num
FROM Person
GROUP BY Email;

SELECT Email FROM 
(SELECT Email, COUNT(Email) AS num
 FROM Person GROUP BY Email) AS temp
WHERE num > 1;

3. 官方解法二：使用 GROUP BY 和 HAVING条件
   • WHERE 和 HAVING 的区别：WHERE过滤行，HAVING过滤分组。
   • 注意：需要先分组才能用COUNT。

SELECT Email
FROM Person
GROUP BY Email
HAVING COUNT(Email) > 1;

参考资料：《MySQL必知必会》第13章

---

183.从不订购的客户

注意：名字相同可能ID不同，因此CustomerId是唯一识别码。

1. 我的解法
   • 先找出有订购记录的客户的ID
   • 然后从所有客户ID中去除有订购记录的客户ID

SELECT Customers.Id FROM Customers, Orders
WHERE Orders.CustomerId = Customers.Id;

SELECT Name AS Customers FROM Customers
WHERE Id NOT IN
(SELECT Customers.Id FROM Customers, Orders
WHERE Orders.CustomerId = Customers.Id);

2. 官方解法

SELECT CustomersId FROM Orders;

SELECT Name AS Customers From Customers
WHERE ID NOT IN
(SELECT CustomerId FROM Orders);

不同之处：我走了弯路。

---

196.删除重复的电子邮箱

注意：题目输出的是Person表，因此需要在原表删除重复的电子邮箱。

• 官方解法：使用 DELETE 和 WHERE 子句
  • 先用自联结SELECT出Email相同但Id大的那些行
  • 将SELECT语句改写为DELETE语句

SELECT P1.* FROM Person AS P1, Person AS P2
WHERE P1.Id > P2.Id 
	AND P1.Email = P2.Email; 

DELETE P1 FROM Person AS P1, Person AS P2
WHERE P1.Id > P2.Id
	AND P1.Email = P2.Email;

这条语句的意思是从Person表中删除满足条件的那些P1行。

这道题不是自己做出来的，需要重点复习！！！

---

197.上升的温度

注意：日期并不一定随着Id的增加而增加，因此不能从Id列下手。

还是用自联结，当天数相差一，温度又满足条件，则SELECT。

SELECT W1.id FROM Weather W1, Weather W2
WHERE W1.Temperature > W2.Temperature
	AND DATEDIFF(W1.recordDate, W2.recordDate) = 1;

注意：这里用到了DATEDIFF日期函数，返回两个日期的差值。

---

596.超过5名学生的课

• 先计算出每门课的选课人数，生成临时表

SELECT class, COUNT(class) AS num
FROM courses GROUP BY class

• 再从临时表中选择出num>5的课程

SELECT class FROM 
(SELECT class, COUNT(class) AS num
FROM courses GROUP BY class) AS temp
WHERE num >= 5;

官方题解中的聚合为 COUNT(DISTINCT student)

---

620.有趣的电影

SELECT * FROM cinema
WHERE description != 'boring' 
	AND id % 2 = 1
ORDER BY -rating;

两个知识点：

1. 奇数可以用 mod(id, 2) = 1 判断
2. 降序可以用 ORDER BY … DESC

---

627.变更性别

• 题目要求在原表修改，用UPDATE
• 新学判断语句的用法

UPDATE Salary 
SET sex = IF(sex='m', 'f', 'm')

UPDATE Salary
SET sex = CASE sex
		    WHEN 'm' THEN 'f'
		    ELSE 'm'
          END;

• MySQL CASE … WHEN用法

CASE sex
	 WHEN '1' THEN 'M'
	 WHEN '2' THEN 'F'
	 ELSE 'MF'
	 END;

---

1179.重新格式化部门表

• 为什么要用SUM？
• 当一个单元格中有多个数据时，CASE WHEN只会提取当中的第一个数据。
• GROUP BY id 会使按照id分组，生成一张虚拟表（假想中的表）。在虚拟表中，所有id=1的revenue或者month数据都写在了同一个单元格中，如8000、7000、6000都是写在同一单元格内的。真正的表是不能这样写的，所以这种写法只存在于虚拟表中，帮助我们理解。

作者：xxiao053
链接：https://leetcode-cn.com/problems/reformat-department-table/solution/guan-yu-group-byyu-sumde-pei-he-by-xxiao053/
来源：力扣（LeetCode）

---

SELECT id, 
    SUM(CASE month WHEN 'Jan' THEN Revenue END) AS 'Jan_Revenue',
    SUM(CASE month WHEN 'Feb' THEN Revenue END) AS 'Feb_Revenue',
    SUM(CASE month WHEN 'Mar' THEN Revenue END) AS 'Mar_Revenue',
    SUM(CASE month WHEN 'Apr' THEN Revenue END) AS 'Apr_Revenue',
    SUM(CASE month WHEN 'May' THEN Revenue END) AS 'May_Revenue',
    SUM(CASE month WHEN 'Jun' THEN Revenue END) AS 'Jun_Revenue',
    SUM(CASE month WHEN 'Jul' THEN Revenue END) AS 'Jul_Revenue',
    SUM(CASE month WHEN 'Aug' THEN Revenue END) AS 'Aug_Revenue',
    SUM(CASE month WHEN 'Sep' THEN Revenue END) AS 'Sep_Revenue',
    SUM(CASE month WHEN 'Oct' THEN Revenue END) AS 'Oct_Revenue',
    SUM(CASE month WHEN 'Nov' THEN Revenue END) AS 'Nov_Revenue',
    SUM(CASE month WHEN 'Dec' THEN Revenue END) AS 'Dec_Revenue'
FROM Department
GROUP BY id