請問獲取訂單金額前10的用戶的SQL怎么寫？

如圖

訂單

orderinfo

執(zhí)行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;

運行結果：

請參考。

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

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

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

在訂單表 多加個字段 存儲下訂單總額 適當冗余 方便查詢

結合 @如來神掌 的回答，針對一個用戶有可能有多個訂單的情況，我修改代碼如下，感覺是可以了....

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;