有如下兩張示例表
訂單金額等于 orderinfo 表里 price*count，每個訂單有多個商品。
請問如何通過SQL語句獲取訂單總金額排前10的用戶？
補充一下：每個用戶也可能有多個訂單哦
6 回答
繁花如伊
TA貢獻2012條經(jīng)驗 獲得超12個贊
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
隔江千里
TA貢獻1906條經(jīng)驗 獲得超10個贊
select * from usertable where id in
(select o.id from orderinfo as o order by o.price*o.count limit 10)
慕容708150
TA貢獻1831條經(jīng)驗 獲得超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
holdtom
TA貢獻1805條經(jīng)驗 獲得超10個贊
結(jié)合 @如來神掌 的回答，針對一個用戶有可能有多個訂單的情況，我修改代碼如下，感覺是可以了....
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;
- 6 回答
- 0 關(guān)注
- 1554 瀏覽
添加回答
舉報
0/150
提交
取消