MySQL的自动化领域（如Excel） [英] Automate MySQL fields (like Excel)

问题描述

说我有喜欢的ID，姓名，分数表。现在通常情况下，获得各小组的排名，我会通过选择所有和秩序。虽然有时，我也不想知道所有的排名，一队仅仅排名。如果我添加了一个列秩，有没有什么办法让MySQL能够自动在这些值填写基于我离分数？ （我相信MS Excel中具有此功能）

Say I have a table like ID, NAME, SCORE. Now normally, to get the rankings of the teams, I'd select all and order by. Sometimes though, I don't want to know all the rankings, just the ranking of one team. If I added a column RANK, is there any way for MySQL to automatically fill in those values for me based off of SCORE? (I believe MS Excel has this capability)

如果是这样，它是如何处理的关系？

and if so, how does it handle ties?

感谢

推荐答案

您可以计算排名时，你让你的查询：

You can calculate the rankings when you make your query:

SELECT * FROM (
    SELECT teams.*, @rownum := @rownum + 1 AS rank
    FROM teams, (SELECT @rownum := 0) T1
    ORDER BY score DESC) T2
WHERE id = 1

它通过初始化称为rownum的变量为0，然后在降低得分的顺序遍历行。对于每个球队ROWNUM增加和团队分配的基础上ROWNUM的当前值一个等级。外选择适用where子句，以便只返回一行。

It works by initializing a variable called rownum to 0 and then iterating over the rows in order of decreasing score. For each team the rownum is increased and the team is assigned a rank based on the current value of rownum. The outer select applies a where clause so that only one row is returned.

下面是分配相同的排名，以团队已追平比分的改进版本：

Here is an improved version that assigns the same rank to teams that have tied scores:

SELECT id, name, teams.score, rank FROM (
    SELECT score, @rownum := @rownum + 1 AS rank
    FROM (SELECT DISTINCT(score) FROM teams) T1, (SELECT @rownum := 0) T2
    ORDER BY score DESC) T3
JOIN teams ON T3.score = teams.score

如果这是不够快你，那么使用触发器来代替。

If this isn't fast enough for you, then use a trigger instead.

这篇关于MySQL的自动化领域（如Excel）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！