复杂的mysql排名!
我有一个包含以下列的表:win、los、id ...
我想按此索引对表进行排序:win / ( win + los ) * 30 + win / SUM(win) * 70 然后找到两个 id 的排名。 我对mysql不太了解,所以我写的完全错误。 (它使用 Perl + DBI + DBD::mysql):
$stmt=$con->prepare("SET @rk := 0");
$stmt=$con->prepare("SELECT rank, id FROM (
SELECT @rk := @rk + 1 AS rank,
(win/(win+los)*30+win/SUM(win)*70) AS index,
win, los, id
FROM tb_name ORDER BY index DESC) as result
WHERE id=? AND id=?");
$stmt -> bind_param ("ii", $id1, $id2);
$stmt -> execute();
$stmt -> bind_result($rk, $idRk);
而且这个查询可能为每个用户每 5-10 秒运行一次,所以我试图找到非常非常快的东西。 如果有必要,我可以添加、更改、删除任何列,以便尽可能快。
I have a table with these columns: win, los, id ...
I want to order the table by this index: win / ( win + los ) * 30 + win / SUM(win) * 70
and then to find the rank for two id's.
I'm not very good on mysql, so what I wrote is totally wrong. (It uses Perl + DBI + DBD::mysql):
$stmt=$con->prepare("SET @rk := 0");
$stmt=$con->prepare("SELECT rank, id FROM (
SELECT @rk := @rk + 1 AS rank,
(win/(win+los)*30+win/SUM(win)*70) AS index,
win, los, id
FROM tb_name ORDER BY index DESC) as result
WHERE id=? AND id=?");
$stmt -> bind_param ("ii", $id1, $id2);
$stmt -> execute();
$stmt -> bind_result($rk, $idRk);
And also this query it suppose to run maybe every 5-10 sec for every user, so I'm trying to find something very, very fast.
If it's necessary I could add, change, delete any column, in order to be as fast as possible.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
试试这个:(
'win', 'los') 上的复合索引可能会很有用。
Try this one:
A composite index on ('win', 'los') might be useful.
无论您在 SELECT 语句中使用什么表达式,也都可以进入 ORDER BY 表达式中。
您还可以为表达式分配一个列别名并按别名排序,就像您所做的那样。
如果您对 mysql 不熟悉,那么我建议您自行开发查询,而不是尝试同时将其嵌入到准备好的语句中。
Whatever experession you use in the SELECT statement can also go into your ORDER BY expression.
You can also assign the expression a column alias and order by the alias, as you've done.
If you're not comfortable with mysql then I suggest you develop your query on its own rather than trying to embed it in a prepared statement at the same time.