mysql - 需要在mysql中准备一个ranklist

Question

我有一张表格，其中包含学生的所有详细信息。我已经为此准备了一个排名程序。例如:

SELECT name, total_marks, @curRank := IF(@prevVal=total_marks, @curRank, @curRank+1) AS rank,
@prevVal:=total_marks
FROM marks, (
SELECT @curRank :=0, @prevVal:=null, @n:=1
) r
ORDER BY total_marks DESC ;

在此声明中。我需要检查是否有学生具有相同的排名。如果是这样，那么我必须检查特定主题标记并相应地对其进行排序。

Answer 1

我认为你可以这样做 -

SELECT rank, GROUP_CONCAT(name) FROM (
  -- your query goes here
  ...
  ...
  ...
) t
GROUP BY rank

---

CREATE TABLE marks(
  name VARCHAR(20),
  z INT(10),
  y INT(10),
  total_marks INT(10),
  rank INT(10)
);

INSERT INTO marks (name, z, y, total_marks, rank) VALUES ('abc', 100, 50, 150, 1);
INSERT INTO marks (name, z, y, total_marks, rank) VALUES ('abc', 100, 50, 150, 2);
INSERT INTO marks (name, z, y, total_marks, rank) VALUES ('abc', 100, 50, 150, 3);

INSERT INTO marks (name, z, y, total_marks, rank) VALUES ('def', 100, 50, 150, 1); 
INSERT INTO marks (name, z, y, total_marks, rank) VALUES ('def', 100, 50, 150, 2); 

INSERT INTO marks (name, z, y, total_marks, rank) VALUES ('abcdef', 100, 50, 150, 1);
INSERT INTO marks (name, z, y, total_marks, rank) VALUES ('abcdef', 100, 50, 150, 2);
INSERT INTO marks (name, z, y, total_marks, rank) VALUES ('abcdef', 100, 50, 150, 3);


SELECT rank, GROUP_CONCAT(name) FROM (
  SELECT name, MAX(rank) rank FROM marks GROUP BY name) t
GROUP BY rank;

+------+--------------------+
| rank | GROUP_CONCAT(name) |
+------+--------------------+
|    2 | def                |
|    3 | abc,abcdef         |
+------+--------------------+

用户 abc 和 abcdef 具有相同的排名 (1,2,3)。用户 def 具有不同的等级 (1,2)。