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

Question

有如下两张示例表

订单金额等于 orderinfo 表里 price*count，每个订单有多个商品。

请问如何通过SQL语句获取订单总金额排前10的用户？

补充一下：每个用户也可能有多个订单哦

Answer 1

如图

订单

orderinfo

执行SQL为：

select sum(a.price * a.count) as total, a.order_id, b.user_id 
from `orderinfo` as a, `order` as b 
where a.order_id=b.id 
group by a.order_id
order by total DESC 
limit 10;

运行结果：

请参考。

Answer 2

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 3

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

Answer 4

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 5

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

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 6

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