有如下兩張示例表
訂單金額等于 orderinfo 表里 price*count，每個(gè)訂單有多個(gè)商品。
請(qǐng)問(wèn)如何通過(guò)SQL語(yǔ)句獲取訂單總金額排前10的用戶(hù)？
補(bǔ)充一下：每個(gè)用戶(hù)也可能有多個(gè)訂單哦
6 回答
繁花如伊
TA貢獻(xiàn)2012條經(jīng)驗(yàn) 獲得超12個(gè)贊
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貢獻(xiàn)1906條經(jīng)驗(yàn) 獲得超10個(gè)贊
select * from usertable where id in
(select o.id from orderinfo as o order by o.price*o.count limit 10)
慕容708150
TA貢獻(xiàn)1831條經(jīng)驗(yàn) 獲得超4個(gè)贊
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貢獻(xiàn)1805條經(jīng)驗(yàn) 獲得超10個(gè)贊
結(jié)合 @如來(lái)神掌 的回答，針對(duì)一個(gè)用戶(hù)有可能有多個(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;
人到中年有點(diǎn)甜
TA貢獻(xiàn)1895條經(jīng)驗(yàn) 獲得超7個(gè)贊
在訂單表 多加個(gè)字段 存儲(chǔ)下訂單總額 適當(dāng)冗余 方便查詢(xún)
- 6 回答
- 0 關(guān)注
- 1563 瀏覽
添加回答
舉報(bào)
0/150
提交
取消