(Mysql)从订单表中找到畅销商品和消费最多的用户，并且对订单表去重

某网站商品的购买订单表为orders，表字段如下：

id	自增id
order_id	订单号id
user_id	用户id
product_id	商品id
time	下单时间
price	订单总价

一、找出销量大于500的商品，按销量排序，销量高的在前。

SELECT product_id,count(*) AS s FROM orders GROUP BY product_id HAVING s>500 ORDER BY s DESC;

二、找出消费最多的10个用户。

SELECT user_id,SUM(price) AS allprice FROM orders GROUP BY user_id ORDER BY allprice DESC LIMIT 10;

三、已知某些错误，同一个订单插入了多条重复的记录（订单号重复）。

1、把所有重复的订单记录列出来

SELECT order_id,COUNT(`order_id`) AS num FROM orders GROUP BY order_id HAVING num>1 ORDER BY num DESC;

2、只保留第一条订单记录，删除掉其它重复的订单

DELETE FROM orders WHERE id NOT IN (
    SELECT * FROM (
         SELECT MIN(id) AS id FROM orders GROUP BY `order_id`
     ) id
);