sql习题

https://leetcode.cn/problems/customer-placing-the-largest-number-of-orders/

SELECT     customer_number FROM     orders GROUP BY      customer_number ORDER BY     COUNT(customer_number) DESC LIMIT 1 

嵌套函数

select customer_number from orders group by customer_number having count(order_number)>=all(     select count(order_number)     from orders     group by customer_number ) 

用窗口函数

select t.customer_number from (select customer_number, rank() over(order by count(order_number) desc) as ranking from orders group by customer_number) t where t.ranking = 1 

rank() over(order by 使用列名)

—rank()over(order by 排名)的结果是不连续的。如果有4人，其中3人并列第一，最终排名结果如下:1 1 1 4

—dense_rank()over(order by 排名)的结果是连续的。如果有4人，其中3人并列第一，最终的排名结果如下:1 1 1 2

注意：使用rank()over(order by 排序字段 排序时，空值最大

若排序字段为null，可能会导致排序null字段排在前面，影响排序的正确性。
所以建议将dense_rank()over(order by 列名 排序)改为dense_rank()over(order by 列名排序 nulls last)
这样只要排序字段为null,会放在最后，不会影响排名结果

组内排序

select sell_date,count(distinct product) as num_sold, group_concat(distinct product order by product asc) as products from Activities   group by sell_date order by sell_date 

SELECT p.product_id,product_name FROM sales s,product p  WHERE s.product_id=p.product_id GROUP BY p.product_id HAVING SUM(sale_date < '2019-01-01')=0 AND SUM(sale_date>'2019-03-31')=0; 

select product_id, product_name from Sales join Product using(product_id) group by product_id having sum(sale_date between "2019-01-01" and "2019-03-31") = count(sale_date)