PHP MYSQL查询结果“RANKING" [英] PHP MYSQL query result "RANKING"

问题描述

我需要得到一个按积分排名的用户列表，并且从我的命令行 (MySQL) 能够生成必要的代码:

I need to get a list of users Ranking by points and from my command line (MySQL) is was able to generate the necessary code:

SET @rank=0;
SELECT rank, iduser, pontos FROM (
SELECT @rank:=@rank+1 AS rank,
       SUM(points.points) AS pontos,
       points.iduser,
       users.name,
       users.idade
  FROM points
       INNER JOIN
       users
       ON (points.iduser = users.id)
 WHERE (users.idade >= %s) AND (users.idade <= %s)
GROUP BY points.iduser ORDER BY pontos DESC) AS totals WHERE iduser = %s

问题是我需要它在 AMFPHP 上运行并且我已经在测试 PHP 文件中对其进行了测试，似乎我不能在同一个mysql_query"中使用 SET 和 SELECT.

The problem is that I need this to run on AMFPHP and I´ve tested it in a test PHP file and seems that I can´t use the SET and SELECT in the same "mysql_query".

我已经看过并且有些人习惯于使用 mysql_query 来执行此操作(我已经对其进行了测试并且可以正常工作)，但是我可以相信这是有效且没有错误的吗?它是否像在 MySQL 事务中一样工作，或者在单独的查询中设置 @rank 可能会导致意外结果?

I´ve looked and some used to mysql_query to do this (I´ve tested it and it works), but can I trust this to be effective and error free? Does it work like in MySQL transactions or setting the @rank in a seperated query may cause unexpected results?

推荐答案

感谢您的快速解答.我选择尝试第一个选项并使用内部联接在选择中设置 @rank 来构建查询.

Thanks for the quick answers. I opted to try the first option and build the query with the inner join setting the @rank in the select.

我不得不稍微改变一下，因为最终结果不是我所期望的，因为我在添加增量后按点数对列表进行排序.我不是 MySQL 专家，但这是我现在所做的工作:

I had to change a litle because the end result wasn´t what I expected as I was ordering the list by the points after adding the incremental. I´m not an expert in MySQL but this is what I made that for now worked:

SELECT rank, pontos FROM (
SELECT @rank:=@rank+1 AS rank, iduser, idade, pontos FROM (
 SELECT SUM(points.points) AS pontos,
 points.iduser,
        users.name,
        users.idade
        FROM points 
        INNER JOIN
        users
        ON (points.iduser = users.id)
 WHERE (users.idade >= 10) AND (users.idade <= 24)
 GROUP BY points.iduser ORDER BY pontos DESC ) AS pointsList
 INNER JOIN
 (SELECT @rank :=0) AS ranker ) AS ranking WHERE iduser = 2

我不得不添加AS"，这样它就不会因为在每个派生表上都没有别名而引发错误....

I had to add the "AS" so that it didn´t throw an error for not having the alias on every derived table....

这篇关于PHP MYSQL查询结果“RANKING"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！