恶补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)