一个 mysql 排序问题

• 请不要在回答技术问题时复制粘贴 AI 生成的内容

This topic created in 4415 days ago, the information mentioned may be changed or developed.

有二个表
1：购买记录表
2：中奖表

两个表都有user那么字段，比如中奖表中 user1 中奖4次，在购买记录里面有10条数据，那么他的中奖率是40%

现在想根据中奖率排序中奖记录表，怎么写？order by 好像不支持子句吧比如 order by (select ...)

中奖

中奖率

记录表

6 replies • 1970-01-01 08:00:00 +08:00

davansy

Apr 21, 2014

尽量不要在数据库中做复杂的操作。楼主的需求可以通过另起一张表单独记录中奖率的信息

skydiver

Apr 21, 2014

根据两个表建立一个view，然后在view里排序

twoconk

Apr 21, 2014 via Android

单独起一张表合适

pythoner

Apr 21, 2014

把中奖率算出来放到其它地方排序

123123

Apr 21, 2014

建个view +1

crazybubble

Apr 21, 2014

SELECT sales_sum.user, sales_sum.deals, jackpot_sum.jackpots
FROM
(SELECT sales.user, count(sales.product_id) AS deals
FROM sales
GROUP BY sales.user) AS sales_sum,
(SELECT jackpot.user, count(jackpot.id) AS jackpots
FROM jackpot
GROUP BY jackpot.user) AS jackpot_sum
WHERE sales_sum.user = jackpot_sum.user
ORDER BY (jackpot_sum.jackpots / sales_sum.deals) DESC;