MySQL帮助：优化根据另一列的顺序设置排名的更新查询教程

Hello StackOverflow社区：

情况

我正在构建一个更新查询,根据每条记录相互比较的方式为表中的所有记录设置排名.表格示例：

id | budget | cost | rank | rank_score  
 1 |   500  |  20  |   ?  |     ?  
 2 |   400  |  40  |   ?  |     ?  
 3 |   300  |  40  |   ?  |     ?

因此,在此表中,成本在确定排名时最重要,其次是预算.因此,记录#2将排名更高,而记录#3将排名第二,#1将排在最后.如您所见,如果两条记录的成本相同,那么预算就会打破平局.

现在,为了轻松跟踪这种“权重”,我正在创建rank\_score列,它将保留成本和预算的连接.因此,上表的rank\_score将是：

id | budget | cost | rank | rank_score  
 1 |   500  |  20  |   ?  |   20500   
 2 |   400  |  40  |   ?  |   40400  
 3 |   300  |  40  |   ?  |   40300  

这个rank\_score可以填充如下：

UPDATE table_name
SET rank_score = CONCAT(cost, budget);

问题

到目前为止,一切都还好.但现在出现了问题.我需要一个仅整数的排名列,用于排序等几个东西,但最重要的是向用户显示他的记录的排名.当然,此排名列将等于rank\_scores的降序.但我找不到在单个更新查询中计算此排名列的方法,而无需执行子查询,php中的循环等.

我曾经尝试过什么

所以,起初我试图获取rank\_score计算,如：

SELECT id,
       CONCAT(cost, budget) AS rank_score
FROM table_name ;

然后在php中循环所有那些rank\_scores,只是为了构建一个类似于的查询：

UPDATE table_name
SET rank_score = CASE id WHEN 1 THEN 20500 END,
    rank = CASE id WHEN 1 THEN 3 END
WHERE id IN (1) ;

…当然,此示例更新查询未完成,因为它对表中的每条记录都有更多WHEN THEN END子句.不用说,这很难看,特别是当你期望拥有成千上万的记录时.

所以,总而言之,我已经有了计算rank\_score的方法,但我也想在同一个查询中计算排名(=排名得分的降序),或者至少没有做那个疯狂的php循环和CASE WHEN THEN END子句.

感谢您阅读和思考;)

澄清

澄清@ SJuan76所说的内容：
我无法通过php分配排名,因为有些用户将一次显示固定数量的记录(例如,他的用户页面：SELECT * WHERE user\_id = 333,可以获取1,3或8条记录)和他需要知道每条记录的排名.在这种情况下通过php分配排名不起作用,因为这样的排名将相对于获取的记录而不是表中的所有记录.

解决方法:

首先,我会将预算,成本和rank\_score更改为整数或其他数值数据类型而不是

UPDATE table_name
SET rank_score = CONCAT(cost, budget) ;

然后你会用：

UPDATE table_name
SET rank_score = cost * 1000 + budget * 1  ;

它更容易,因为你不必处理字符串函数,并有类似的东西：

SELECT * 
FROM table_name
WHERE (conditions...)
ORDER BY rank_score DESC

(括号：将一个参数(1000)设置为高于另一个(1)相当于具有成本,预算的顺序.请尝试以下方法检查：

SELECT * 
FROM table_name
ORDER BY cost DESC
       , budget DESC

因此,您可以完全放弃rank\_score,除非您当然打算使用各种参数值进行实验.

正如其他人所指出的那样,拥有一个不存储数据而是存储计算的字段并非最佳做法.这是非正常化. Instaed,您保持表的规范化,让数据库在您每次需要时进行计算：

SELECT id, budget, cost, 
       cost*1000 + budget*1 AS rank_score_calculated
FROM table_name
ORDER BY rank_score_calculated DESC

rank\_score\_calculated未存储在上面的示例中.这样,每次更改预算或成本或在表中添加新行时,您都不必更新计算字段.

只有一个缺点.如果表格非常大并且您需要经常进行大量用户的查询(和计算),并且表格经常更新,那么它可能会降低数据库的速度.在这种情况下,人们应该开始考虑添加这样的字段.

另一种情况是当需要所有表行的绝对排名时,就像你需要的那样.因为MySQL没有“窗口”功能,所以在纯SQL中编写这样的查询非常困难.)

可以使用MySQL变量计算排名

SELECT *
     , @rownum:=@rownum+1 AS rank_calculated
FROM table_name
   , (SELECT @rownum:=0) AS st
ORDER BY rank_score DESC

如果您想将这些值放入等级,请使用：

UPDATE table_name
         JOIN
         ( SELECT id
                , @rownum:=@rownum+1 AS rank_calculated
           FROM table_name
              , (SELECT @rownum:=0) AS st
           ORDER BY rank_score DESC
         ) AS r
         ON r.id = table_name.id
SET table_name.rank = r.rank_calculated ;

以上两个查询不是纯SQL.您可以检查移动到另一个支持窗口功能的daabase系统的选项,如Postgres,SQL-Server或Oracle.