在 mySQL 和 VIEW 创建中分配排名 [英] Assiging rank in mySQL and VIEW creation

问题描述

我有一个名为积分榜的表格，其中包含每支球队的得分、净胜球和进球数.这是表结构和数据.

I have a table called standings which has points, goal difference and goals scored by each team. Here's the table structure and the data.

CREATE TABLE standings (
  team_id int(3) unsigned NOT NULL AUTO_INCREMENT,
  points int(2) unsigned DEFAULT 0,
  goal_difference int(2) unsigned DEFAULT 0,
  goals_for int(2) unsigned DEFAULT 0,
  PRIMARY KEY (team_id)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1;

insert into standings(team_id,points,goal_difference,goals_for) values (1,20,2,17);
insert into standings(team_id,points,goal_difference,goals_for) values (2,14,8,15);
insert into standings(team_id,points,goal_difference,goals_for) values (3,9,2,11);
insert into standings(team_id,points,goal_difference,goals_for) values (4,14,10,12);
insert into standings(team_id,points,goal_difference,goals_for) values (5,17,10,19);
insert into standings(team_id,points,goal_difference,goals_for) values (6,5,-11,7);
insert into standings(team_id,points,goal_difference,goals_for) values (7,14,10,10);
insert into standings(team_id,points,goal_difference,goals_for) values (8,9,2,14);
insert into standings(team_id,points,goal_difference,goals_for) values (9,12,1,10);
insert into standings(team_id,points,goal_difference,goals_for) values (10,9,2,14);
commit;

我想按照分数、goal_difference 和goals_for 的降序对这个表进行排序，并根据这个顺序为每个团队分配排名.由于 mySQL 没有 RANK 功能，搜索这个网站后我来了完成此查询.

I want to sort this table in descending order of points, goal_difference and goals_for and assign rank to each team based on this order. Since mySQL doesn't have RANK functions, after searching this site I came up with this query.

SELECT CASE
          WHEN @prev_value = concat(points,'-',goal_difference,'-',goals_for)
          THEN
             @cur_rank
          WHEN @prev_value := concat(points,'-',goal_difference,'-',goals_for)
          THEN
             @cur_rank := @cur_rank + 1
       END
          AS rank, s.team_id, s.points, s.goal_difference, s.goals_for
  FROM standings s, (SELECT @cur_rank := 0) p, (SELECT @prev_rank := 0) q, (SELECT     @prev_value := NULL) r
 ORDER BY s.points DESC, s.goal_difference DESC, s.goals_for DESC;

到目前为止一切顺利.现在我有两个问题.

So far so good. Now I have two questions.

1. 如果您看到上述查询的结果，则第 7 位的第 8 队和第 10 队之间并列平局.所以，我想将排名第 9 的球队分配给下一个排名第 3 的团队.我该如何做到这一点，而无需在查询中添加更多列.

2. 我想使用这个查询创建一个视图.但是 mySQL 不允许我创建一个并给出错误，视图的 SELECT 包含一个变量或参数".请建议如何为此创建 VIEW.

1. If you see the result of above query, there is tie between team 8 and 10 for 7th position. So, I want to assign rank no 9 to the next team no 3. How do I do this, without adding any more columns in the query.

2. I want to create a VIEW using this query. But mySQL doesn't let me create one and gives error, 'View's SELECT contains a variable or parameter'. Please suggest how to create VIEW for this.

CREATE VIEW view_standings
AS
   SELECT CASE
     WHEN @prev_value = concat(points,'-',goal_difference,'-',goals_for)
     THEN
        @cur_rank
     WHEN @prev_value := concat(points,'-',goal_difference,'-',goals_for)
     THEN
        @cur_rank := @cur_rank + 1
  END
     AS rank,s.team_id,s.points,s.goal_difference,s.goals_for
FROM standings s,(SELECT @cur_rank := 0) p,(SELECT @prev_rank := 0) q,(SELECT @prev_value := NULL) r
ORDER BY s.points DESC, s.goal_difference DESC, s.goals_for DESC;

推荐答案

您还可以使用相关子查询进行排名.如果您拥有中等数量的数据，这可能会证明计算量很大.

You can also do rankings using correlated subqueries. If you have even a moderate amount of data, this might prove computationally intensive.

 select s.*,
        (select 1+COUNT(*)
         from standings s2
         where s2.points > s.points or
               (s2.points = s.points and s2.goal_difference > s.goal_difference) or
               (s2.points = s.points and s2.goal_difference = s.goal_difference and s2.goals_for > sys.goals_for
        ) as ranking
 from standings s

因为它在 from 子句中只有一个子查询，所以您可以将其用作视图.

Because it only has a subquery in the from clause, you can use this as a view.

我认为你可以通过索引没有standings(points,goal_difference,goals_for)来提高表现.

I think you can improve performance by having an index no standings(points, goal_difference, goals_for).

这篇关于在 mySQL 和 VIEW 创建中分配排名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！