請(qǐng)問(wèn)獲取訂單金額前10的用戶的SQL怎么寫(xiě)？

如圖

訂單

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;

運(yùn)行結(jié)果：

請(qǐng)參考。

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

在訂單表 多加個(gè)字段 存儲(chǔ)下訂單總額 適當(dāng)冗余 方便查詢

結(jié)合 @如來(lái)神掌 的回答，針對(duì)一個(gè)用戶有可能有多個(gè)訂單的情況，我修改代碼如下，感覺(jué)是可以了....

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;