1965.丢失信息的雇员

表: Employees
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| employee_id | int     |
| name        | varchar |
+-------------+---------+
employee_id 是这个表的主键。每一行表示雇员的id 和他的姓名。
表: Salaries
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| employee_id | int     |
| salary      | int     |
+-------------+---------+
employee_id is 这个表的主键。每一行表示雇员的id 和他的薪水。

写出一个查询语句，找到所有 雇员的 姓名 丢失了，或者雇员的 薪水信息 丢失了的雇员id。
返回这些雇员的id  employee_id ， 从小到大排序 。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

答案解析

联表查询，全连接，但是mysql不值钱完全外连接， 所有可以有左连接和有连接拼起来
Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；

SELECT A.employee_id FROM employees A LEFT JOIN salaries B ON A.employee_id = B.employee_id
WHERE B.salary IS NULL
UNION
SELECT B.employee_id FROM employees A RIGHT JOIN salaries B ON A.employee_id = B.employee_id
WHERE A.name IS NULL
ORDER BY employee_id
1
2
3
4
5
6

1795.每个产品在不同商店的价格

表：Products
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| product_id  | int     |
| store1      | int     |
| store2      | int     |
| store3      | int     |
+-------------+---------+
这张表的主键是product_id（产品Id）。
每行存储了这一产品在不同商店store1, store2, store3的价格。如果这一产品在商店里没有出售，则值将为null。
请你重构 Products 表，查询每个产品在不同商店的价格，
使得输出的格式变为(product_id, store, price) 。如果这一产品在商店里没有出售，则不输出这一行。
输出结果表中的 顺序不作要求 。
输入：
Products table:
+------------+--------+--------+--------+
| product_id | store1 | store2 | store3 |
+------------+--------+--------+--------+
| 0          | 95     | 100    | 105    |
| 1          | 70     | null   | 80     |
+------------+--------+--------+--------+
输出：
+------------+--------+-------+
| product_id | store  | price |
+------------+--------+-------+
| 0          | store1 | 95    |
| 0          | store2 | 100   |
| 0          | store3 | 105   |
| 1          | store1 | 70    |
| 1          | store3 | 80    |
+------------+--------+-------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

答案解析

行转列用groupby，列转行用union all
union all 和 union 区别是 union all 不去重

sumif

SELECT product_id, 'store1' store,store1 price 
FROM products
WHERE store1 IS NOT NULL
UNION ALL
SELECT product_id, 'store2' store,store2 price 
FROM products
WHERE store2 IS NOT NULL
UNION ALL
SELECT product_id, 'store3' store,store3 store 
FROM products
WHERE store3 IS NOT NULL
1
2
3
4
5
6
7
8
9
10
11

608.树节点

给定一个表 tree，id 是树节点的编号， p_id 是它父节点的 id 。
+----+------+
| id | p_id |
+----+------+
| 1  | null |
| 2  | 1    |
| 3  | 1    |
| 4  | 2    |
| 5  | 2    |
+----+------+
树中每个节点属于以下三种类型之一：
-叶子：如果这个节点没有任何孩子节点。
--根：如果这个节点是整棵树的根，即没有父节点。
--内部节点：如果这个节点既不是叶子节点也不是根节点。
写一个查询语句，输出所有节点的编号和节点的类型，并将结果按照节点编号排序。上面样例的结果为：
+----+------+
| id | Type |
+----+------+
| 1  | Root |
| 2  | Inner|
| 3  | Leaf |
| 4  | Leaf |
| 5  | Leaf |
+----+------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

答案解析

not in与null值, 如果not in 集合里有null值，那直接返回null 对应结果为false
简单的case语句的几种用法

CASE WHEN condition THEN result ELSE result END

CASE WHEN p_id IS NULL THEN 'Root'
	 WHEN id NOT IN(SELECT p_id FROM tree WHERE p_id IS NOT NULL) THEN 'Leaf' ELSE 'Inner' END
	 
CASE SCORE WHEN 'A' THEN '优' ELSE '不及格' END
1
2
3
4
5
6

答案：

SELECT 
	id, 
	CASE WHEN p_id IS NULL THEN 'Root'
	 	 WHEN id NOT IN(SELECT p_id FROM tree WHERE p_id IS NOT NULL) THEN 'Leaf' ELSE 'Inner' END AS 'type'
FROM tree
1
2
3
4
5

176.第二高的薪水

Employee 表：
+-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| salary      | int  |
+-------------+------+
id 是这个表的主键。
表的每一行包含员工的工资信息。
编写一个 SQL 查询，获取并返回 Employee 表中第二高的薪水 。如果不存在第二高的薪水，查询应该返回 null 。
输入：
Employee 表：
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+
输出：
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200                 |
+---------------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

答案解析

limit 用法：

select * from tableName limit i,n
# tableName：表名
# i：为查询结果的索引值(默认从0开始)，当i=0时可省略i
# n：为查询结果返回的数量
# i与n之间使用英文逗号","隔开
1
2
3
4
5

关键字 distinct 去重

SELECT DISTINCT name from A
1

查询第二大， 先查询全部，然后去重，然后降序排序，然后获取limit获取第二个
如果上面的操作没有查到值，那就返回null， 然后给查询内容命名

SELECT IFNULL(
    (SELECT DISTINCT salary
	FROM employee 
	ORDER BY salary DESC
	LIMIT 1, 1), null) SecondHighestSalary 
1
2
3
4
5

总结：

1. MySQL没有全外连接，用左右连接 + union all
2. 把整行的拆散为多行用union， 把多行合并成整行用 group by
3. 如果not in 集合里有null值，那直接返回null 对应结果为false，不会真正判断有没有在集合里
4. case 语句用法 CASE WHEN condition THEN result1 ELSE result2 END
5. DISTINCT去重 SELECT DISTINCT name from A
6. LIMIT 用法 取i - n,从0开始： SELECT * FROM table limit i,n