쿼리는 총점에 따라 사용자의 순위를 잘못 설정합니다.

쿼리는 총점에 따라 사용자의 순위를 잘못 설정합니다.

저는 사용자당 총점을 모두 얻고 내림차순으로 정렬하여 순위를 인쇄하려고 합니다.내 쿼리는 거의 정상적으로 작동하지만 사용자의 순위를 제대로 설정하지 못합니다.

SET @rank := 0;
SELECT (@rank := @rank + 1) AS rank, u.username, SUM(s.score) AS totalScore
FROM solution AS s
INNER JOIN users u ON u.id = s.author_id
GROUP BY u.username
ORDER BY totalScore DESC

사용자 스키마:

|---------------------|------------------|
|         id          |     username     |
|---------------------|------------------|
|          1          |       test1      |
|---------------------|------------------|
|          2          |       test2      |
|---------------------|------------------|
|          3          |       test3      |
|---------------------|------------------|
|          8          |       test4      |
|---------------------|------------------|

솔루션 스키마:

|---------------------|------------------|-----------------|
|         id          |     author_id    |      score      |
|---------------------|------------------|-----------------|
|          1          |       1          |       55        |
|---------------------|------------------|-----------------|
|          2          |       2          |       5         |
|---------------------|------------------|-----------------|
|          3          |       3          |       22        |
|---------------------|------------------|-----------------|
|          4          |       8          |       43        |
|---------------------|------------------|-----------------|
|          5          |       8          |       43        |
|---------------------|------------------|-----------------|

결과는 다음과 같습니다.

|---------------------|------------------|-----------------|
|         rank        |     username     |    totalScore   |
|---------------------|------------------|-----------------|
|          4          |       test4      |       86        |
|---------------------|------------------|-----------------|
|          1          |       test1      |       55        |
|---------------------|------------------|-----------------|
|          3          |       test3      |       22        |
|---------------------|------------------|-----------------|
|          2          |       test2      |       5         |
|---------------------|------------------|-----------------|

왜 그런 일이 일어날까요?

예상 결과는 다음과 같습니다.

|---------------------|------------------|-----------------|
|         rank        |     username     |    totalScore   |
|---------------------|------------------|-----------------|
|          1          |       test4      |       86        |
|---------------------|------------------|-----------------|
|          2          |       test1      |       55        |
|---------------------|------------------|-----------------|
|          3          |       test3      |       22        |
|---------------------|------------------|-----------------|
|          4          |       test2      |       5         |
|---------------------|------------------|-----------------|

제가 댓글로 말씀드렸듯이.먼저 점수를 합산하여 순위를 매깁니다.

SELECT 
    (@rank:=@rank + 1) rank, username, totalscore
FROM
    (SELECT 
        u.username, SUM(s.score) AS totalScore
    FROM
        solution AS s
    INNER JOIN users u ON u.id = s.author_id
    GROUP BY u.username
    ORDER BY totalScore DESC) t1,
    (SELECT @rank:=0) r1

두 번째 쿼리가 내 것인 예를 참조

스키마(MySQL v5.7)

CREATE TABLE users (
  `id` INTEGER,
  `username` VARCHAR(5)
);

INSERT INTO users
  (`id`, `username`)
VALUES
  ('1', 'test1'),
  ('2', 'test2'),
  ('3', 'test3'),
  ('8', 'test4');

CREATE TABLE solution (
  `id` INTEGER,
  `author_id` INTEGER,
  `score` INTEGER
);

INSERT INTO solution
  (`id`, `author_id`, `score`)
VALUES
  ('1', '1', '55'),
  ('2', '2', '5'),
  ('3', '3', '22'),
  ('4', '8', '43'),
  ('5', '8', '43');

---

쿼리 #1

SET @rank := 0;

표시할 결과가 없습니다.

---

쿼리 #2

SELECT (@rank := @rank + 1) AS rank, u.username, SUM(s.score) AS totalScore
FROM solution AS s
INNER JOIN users u ON u.id = s.author_id
GROUP BY u.username
ORDER BY totalScore DESC;

| rank | totalScore | username |
| ---- | ---------- | -------- |
| 4    | 86         | test4    |
| 1    | 55         | test1    |
| 3    | 22         | test3    |
| 2    | 5          | test2    |

---

쿼리 #3

SELECT
(@rank := @rank +1) rank
,username
,totalscore
FROM
(SELECT
u.username,
SUM(s.score) AS totalScore
FROM solution AS s
INNER JOIN users u ON u.id = s.author_id
GROUP BY u.username
ORDER BY totalScore DESC) t1,(SELECT @rank := 0) r1;

| rank | username | totalscore |
| ---- | -------- | ---------- |
| 1    | test4    | 86         |
| 2    | test1    | 55         |
| 3    | test3    | 22         |
| 4    | test2    | 5          |

---

DB Fiddle에서 보기

MySql에서 작동합니다.

Select username, totalScore, Rank() over (order by totalScore desc) as Rank 
from (
SELECT  u.username, SUM(s.score) AS totalScore
FROM solution AS s
INNER JOIN users u ON u.id = s.author_id
GROUP BY u.username
ORDER BY totalScore DESC
)t

언급URL : https://stackoverflow.com/questions/61276663/query-improperly-sets-rank-on-user-depending-on-his-total-points