LeetCode---SQL刷题3

1.1965. 丢失信息的雇员

SQL语句如下:

SELECT e.employee_id
FROM Employees e
LEFT JOIN Salaries s  ON e.employee_id = s.employee_id
WHERE salary IS  NULL 
UNION ALL
SELECT s.employee_id
FROM Employees e
RIGHT JOIN Salaries s  ON e.employee_id = s.employee_id
WHERE name IS NULL
ORDER BY employee_id
1
2
3
4
5
6
7
8
9
10

备注:根据薪水和姓名为NULL可知使用全连接进行查询，由于MySQL中不存在全连接所以使用左连接+右连接的方式实现。

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

SQL语句如下:

SELECT product_id,'store1' AS store,store1  price 
FROM Products
WHERE store1 IS NOT NULL

UNION

SELECT product_id,'store2' AS store,store2  AS price 
FROM Products
WHERE store2 IS NOT NULL

UNION

SELECT product_id,'store3' AS store,store3  AS price 
FROM Products
WHERE store3 IS NOT NULL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

备注:根据输出结果可知,这是一道列转行的SQL题。

如果是行转列的话操作如下:

SELECT 
  product_id,
  SUM(IF(store = 'store1', price, NULL)) 'store1',
  SUM(IF(store = 'store2', price, NULL)) 'store2',
  SUM(IF(store = 'store3', price, NULL)) 'store3' 
FROM
  Products1 
GROUP BY product_id ;
1
2
3
4
5
6
7
8

表Products1结构如下:

SQL输出结果如下:

3.608. 树节点

SQL语句如下:

• 解法1

SELECT id,
IF(p_id IS NULL,'Root',
    IF(
       id IN  (SELECT p_id FROM tree),
        'Inner',
        'Leaf'
      )
) AS type  
FROM tree
ORDER BY id;
1
2
3
4
5
6
7
8
9
10

• 解法2

SELECT id,
CASE 
WHEN p_id IS NULL THEN 'Root'
WHEN id IN (SELECT p_id FROM tree) THEN 'Inner'
ELSE 'Leaf'
END
AS type
FROM tree
ORDER BY id;
1
2
3
4
5
6
7
8
9

• 解法3

SELECT id,'Root' AS type
FROM tree
WHERE p_id IS NULL

UNION

SELECT id,'Inner' AS type
FROM tree
WHERE  p_id IS NOT NULL AND id in (SELECT p_id FROM tree WHERE p_id IS NOT NULL)


UNION

SELECT id,'Leaf' AS type
FROM tree
WHERE p_id IS NOT NULL AND id NOT IN (SELECT p_id FROM tree WHERE p_id IS NOT NULL)
ORDER BY id;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

4.175. 组合两个表

SQL语句如下:

SELECT firstName,lastName,city,state
FROM Person p
LEFT JOIN Address a ON p.personId = a.personId
1
2
3

5.176. 第二高的薪水

SQL语句如下:

SELECT
IFNULL((
    SELECT DISTINCT salary  
    FROM Employee 
    ORDER BY salary DESC
    LIMIT 1,1
),NULL) AS SecondHighestSalary
1
2
3
4
5
6
7

6.1581. 进店却未进行过交易的顾客

SQL语句如下:

SELECT customer_id,COUNT(visit_id)  AS count_no_trans
FROM Visits
WHERE visit_id NOT IN (SELECT visit_id  FROM Transactions)
GROUP BY customer_id;
1
2
3
4

7.1148. 文章浏览 I

SQL语句如下:

SELECT DISTINCT author_id AS id
FROM Views 
WHERE author_id  = viewer_id
ORDER BY author_id;
1
2
3
4