4.组合查询 & 指定选取

1.1965.丢失信息的雇员

 

# Write your MySQL query statement below
SELECT
   employee_id
FROM
   Employees
WHERE
   employee_id not in (select employee_id from Salaries)
UNION
SELECT
   employee_id
FROM
   Salaries
WHERE
   employee_id not in (select employee_id from Employees)
ORDER BY employee_id;

 

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

MySQL列转行、行转列 - 每个产品在不同商店的价格 - 力扣（LeetCode）

第2行必须 ‘store1 ’ 而 store1不可以：因为 不加引号是给原有的store1这一列取别名，而值还是原来的值，也就是那些价格数字; 加了引号是说创造一个新的列叫store，这一列的所有值都是"store1"

“ ‘store1’  store ” 这个store1是一个字符串，是store属性那一列的值

“ ‘store1 price’ ” 这个store1是一个int型数据，是原来Products表里store1属性那一列的值

# Write your MySQL query statement below
SELECT product_id,'store1' /*as*/ store,store1 price 
FROM Products 
WHERE store1 IS NOT NULL
UNION
SELECT product_id,'store2' store,store2 price 
FROM Products 
WHERE store2 IS NOT NULL
UNION
SELECT product_id,'store3' store,store3 price 
FROM Products 
WHERE store3 IS NOT NULL;

 

3.608树节点

 i.第12行要加后面的条件 p_id IS NOT NULL ，因为当只有一个根节点时，集合(select p_id from tree where p_id IS NOT NULL)里没有元素，自然就满足了id not in ......的条件

# Write your MySQL query statement below
SELECT id,'Root' type
FROM tree
WHERE p_id IS NULL
 UNION
SELECT id,'Inner' type
FROM tree
WHERE id in (select /*distinct*/ p_id from tree) and p_id IS NOT NULL
 UNION
SELECT id,'Leaf' type
FROM tree
WHERE id not in (select p_id from tree where p_id IS NOT NULL) and p_id IS NOT NULL
ORDER BY id;

ii.第6行比i中解法中第12行相比少了后面的条件p_id IS NOT NULL，因为用的是控制流case语句，如果到了leaf条件这里，说明不满足前面的条件 p_id IS NULL,所以p_id 肯定是IS NOT NULL的

# Write your MySQL query statement below
SELECT id,
    CASE
      WHEN p_id IS NULL
       THEN 'Root'
      WHEN id NOT IN (SELECT DISTINCT atree.p_id FROM tree atree WHERE atree.p_id IS NOT NULL)
       THEN 'Leaf'
      ELSE
            'Inner'
    END type
FROM 
   tree
ORDER BY id;

iii.还可用if语句（第二个IF语句内条件没加p_id IS NOT NULL 原因与ii一样）

# Write your MySQL query statement below
SELECT id,
    IF(ISNULL(p_id),'Root',IF(tree.id IN (SELECT DISTINCT atree.p_id FROM tree atree WHERE atree.p_id IS NOT NULL),'Inner','Leaf')) type
FROM 
   tree
ORDER BY id;

4.176.第二高的薪水

 i.

IFNULL(f1,f2)：如果f1不为空 则返回f1的值，否则返回f2的值

LIMIT nums1 OFFSET nums2：跳过nums2个数字后 选nums1个数字  (21条消息) sql中limit和offset用法_华山栈道的博客-CSDN博客_sql中limit和offset

 

 

# Write your MySQL query statement below
SELECT IFNULL(
    (SELECT DISTINCT salary 
    FROM Employee
    ORDER BY salary DESC
    LIMIT 1 OFFSET 1),null) SecondHighestSalary;
/*FROM Employee;*/ 因为后面没加where等条件限制 如果最后加了这一行那么原表中salary有几行数据，结果就会有几行数据。如果硬要加这一行的话 要在第2行SELECT 后面加一个DISTINCT

 

ii.选第二高还可以，但选第三高第四高......就麻烦了

 

# Write your MySQL query statement below
SELECT MAX(salary) SecondHighestSalary
FROM 
   Employee
WHERE 
   salary < (SELECT MAX(salary) FROM Employee);