sql-高级查询和连接

1731. 每位经理的下属员工数量 - 力扣（LeetCode）

select e2.employee_id,
    e2.name,
    COUNT(e1.employee_id) AS reports_count,
    round(avg(e1.age),0) as average_age 
FROM Employees e1
join Employees e2
    ON e1.reports_to = e2.employee_id
GROUP BY e2.name,
    e2.employee_id 
ORDER BY e2.employee_id

　　两张表，一张经理，一张员工表，理清之间的联系即可

1789. 员工的直属部门 - 力扣（LeetCode）

SELECT employee_id,
if(count(department_id)=1,department_id,max(case when primary_flag='Y' then department_id end)) 
    as department_id 
FROM Employee
group by employee_id 

　　注意：

MAX 在这里不是用来比较大小，而是作为一种从多个值（主要是 NULL 和一个有效值）中选择非 NULL 值的机制。做一个选择器的作用，区别于null。

MIN也是可以的，只要能区分null

610. 判断三角形 - 力扣（LeetCode）

select x,y,z,
    if((x+y>z)and (x+z>y) and (y+z>x),'Yes','No')
    as triangle 
from Triangle 

　　注意：这不像其他语言，可以连等，sql只能一个个判断用and连接

(x+y>z) and (z+x>y) and (y+z>x)

180. 连续出现的数字 - 力扣（LeetCode）

select distinct l3.num as ConsecutiveNums 
from logs l1
join logs l2
    on l1.id = l2.id - 1
join logs l3
    on l2.id = l3.id - 1
where l1.num = l2.num and l2.num = l3.num

建立三张表，这三张表都出现了该数字就满足条件，取出

注意，不能写l1.num = l2.num = l3.num 不能连等，要分段判断

 

1164. 指定日期的产品价格 - 力扣（LeetCode）

select product_id,
    new_price  as price 
from Products 
where (product_id,change_date) in 
    (
        select product_id,
            max(change_date)as change_date
        from Products 
        where change_date <= '2019-08-16'
        group by product_id
    )
union all
(
    select product_id,
        10 as price
    from Products
    group by product_id
    having min(change_date) > '2019-08-16'
)

　　

1204. 最后一个能进入巴士的人 - 力扣（LeetCode）

select t.person_name
from 
(
    select person_name,
    sum(weight) over(order by turn) as weight
    from Queue 
) t
where t.weight <= 1000
order by Weight desc
limit 1

　　注意开窗函数的使用，聚合函数也可以作为开窗函数

1907. 按分类统计薪水 - 力扣（LeetCode）

select 'Low Salary' as category,
    sum(income < 20000)AS accounts_count
from accounts
union all
select 'Average Salary' category,
sum(income >= 20000 and income <= 50000)
from accounts
union all
SELECT 'High Salary', 
    sum(income > 50000) 
FROM accounts

　　三种情况并列，用union all

区别：

　　union会自动去除重复行，且性能较慢