Hello StackOverflow社区:
形势
我正在构建一个更新查询,根据每个记录之间的比较情况为表中的所有记录设置排名。表格示例:
id | budget | cost | rank | rank_score
1 | 500 | 20 | ? | ?
2 | 400 | 40 | ? | ?
3 | 300 | 40 | ? | ?
因此,在该表中,cost
在确定秩时具有最大的权重,其次是budget
。因此,记录#2将排名更高,而记录#3将排名第二,#1将排名最后。正如您所看到的,如果两条记录具有相同的cost
,那么budget
将打破平局。
现在,为了方便地跟踪这样的"权重",我正在创建rank_score
列,它将包含cost
和budget
的串联。因此,上表的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
列来进行排序等操作,但最重要的是向用户显示其记录的级别。当然,此rank
列将等于rank_scores的降序。但是,如果不进行子查询、php中的循环等操作,我无法找到在单个更新查询中计算rank
列的方法。
我尝试了什么
所以,一开始我试图获取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
的方法,但我也想在同一个查询中计算rank
(=排名分数的降序(,或者至少不用做疯狂的php循环和CASE WHEN THEN END
子句。
感谢您对此的阅读和思考;(
澄清
澄清@SJuan76的说法:我无法通过php分配排名,因为在某些情况下,用户一次会看到固定数量的记录(例如,他的用户页面:SELECT * WHERE user_id = 333
,可以获取1、3或8条记录(,他需要知道每条记录的排名。在这种情况下,通过php分配秩是不起作用的,因为这样的秩将相对于提取的记录,而不是表中的所有记录。
首先,我会将budget
、cost
和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
(相当于具有cost, budget
的阶数。试试这个来检查:
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
CCD_ 26在上述示例中没有被存储。这样,您就不必每次更改预算或成本或在表中添加新行时都更新计算字段。
只有一个缺点。如果表真的很大,并且需要很多用户经常进行查询(和计算(,并且表经常更新,那么这可能会降低数据库的速度。在这种情况下,应该开始考虑添加这样一个字段。
另一种情况是,在所有表行中都需要一个绝对的rank
,就像您的需要一样。因为MySQL没有"窗口"函数,所以很难用纯SQL编写这样的查询。(
排名可以使用MySQL变量计算
SELECT *
, @rownum:=@rownum+1 AS rank_calculated
FROM table_name
, (SELECT @rownum:=0) AS st
ORDER BY rank_score DESC
如果您想将这些值放入rank
中,请使用:
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。您可以选择转移到另一个支持窗口功能的数据库系统,如Postgres、SQL Server或Oracle。
您是否尝试过将其拆分为两个查询?或者使用子查询?
mysql> select p.*, (select count(0)+1 from table_name as s where s.cost >= p.cost and s.budget < p.budget) as rank from table_name as p where p.id in (1,2,3);
+----+------+--------+------+
| id | cost | budget | rank |
+----+------+--------+------+
| 1 | 20 | 500 | 3 |
| 2 | 40 | 400 | 2 |
| 3 | 40 | 300 | 1 |
+----+------+--------+------+
3 rows in set (0.00 sec)
SQL已经可以随心所欲地对记录进行排序,而不需要额外的表空间,而且(更重要的是(不需要破坏正常的形式。
排名可以直接从排序中获得,只需询问您想要的排序,当您检索数据时,在程序代码中根据其顺序添加索引。
为什么要在数据库中执行此操作?