请问获取订单金额前10的用户的SQL怎么写？

Question

有如下两张示例表 订单金额等于 orderinfo 表里 price*count，每个订单有多个商品。 请问如何通过SQL语句获取订单总金额排前10的用户？ 补充一下：每个用户也可能有多个订单哦

Answer 1

select userid from order a left join (select * from (select orderid,sum(price*count) as money from orderinfo group by orderid) order by money desc limit 10) b on a.id = b.orderid

Answer 2

select * from usertable where id in
(select o.id from orderinfo as o order by o.price*o.count limit 10)

Answer 3

select a.user_id from order as a INNER JOIN order_info as b ON a.id=b.orderid ORDER BY(b.price*b.count) desc limit 10

Answer 4

结合 @如来神掌 的回答，针对一个用户有可能有多个订单的情况，我修改代码如下，感觉是可以了....

SELECT sum(b.total),userid FROM `order` AS a,
(SELECT SUM(price*count) AS total,orderid 
FROM `orderinfo`
GROUP BY `orderid`) AS b
WHERE a.`id` = b.`orderid`
GROUP BY a.`userid`
ORDER BY sum(b.`total`) DESC
limit 10;

Answer 5

在订单表 多加个字段 存储下订单总额 适当冗余 方便查询