Leetcode_1082_销售分析1_SQL

恶补SQLing

第一种写法，需要注意的点是all
having是和group by连用时候的where

IN、ALL、ANY、SOME的解释

• IN:在范围内的值，只要有就true
• ALL: 与子查询返回的所有值比较为true 则返回true
• ANY:与子查询返回的任何值比较为true 则返回true
• SOME:是ANY的别称,很少用

SELECT seller_id
from sales
group by seller_id
having SUM(price) >= ALL (
    select SUM(price)
    from sales
    group by seller_id
)

第二种我就是先找和最大的price，通过排序+limit0, 1找最大值，然后比较sum

SELECT seller_id
from sales
group by seller_id
having SUM(price) = (
    select SUM(price) as sum
    from sales
    group by seller_id
    order by sum desc
    limit 0, 1
)

第三种使用到 RANK() OVER(ORDER BY SUM(price) DESC) 函数
获取rank为1的seller

select seller_id
from (
    SELECT seller_id , RANK() OVER(ORDER BY SUM(price) DESC) AS rk
    from sales
    group by seller_id
) as t
where t.rk = 1

第四种采用with

with temp as (
    select SUM(price) as sum
    from sales
    group by seller_id
)

SELECT seller_id
from sales
group by seller_id
having SUM(price) = (select max(sum) from temp)