Целое число, увеличенное на отображаемую строку

Это командная таблица:



+----+-------+--------+-------+
| id | alias | pwd    | score |
+----+-------+--------+-------+
|  1 | login | mdp    |     5 |
|  2 | azert | qsdfgh |    50 |
|  3 | test  | test   |   780 |
+----+-------+--------+-------+

Это таблица активности

+----+--------------+---------------------+-------+--------+
| id | localisation | name                | point | answer |
+----+--------------+---------------------+-------+--------+
|  1 | Madras       | Lancement du projet |     0 | NULL   |
|  2 | Valparaiso   | act1                |   450 | un     |
|  3 | Amphi        | act2                |    45 | deux   |
|  4 | Amphix       | act3                |   453 | trois  |
|  5 | Amphix       | act4                | 45553 | qautre |
|  6 | Madras       | Lancement du projet |     0 | NULL   |
|  7 | Valparaiso   | act1                |   450 | un     |
|  8 | Amphi        | act2                |    45 | deux   |
|  9 | Amphix       | act3                |   453 | trois  |
| 10 | Amphix       | act4                | 40053 | fin    |
+----+--------------+---------------------+-------+--------+

Это таблица подачи:

+--------+---------------------+------------+--------+
| FeedId | ts                  | ActivityId | TeamId |
+--------+---------------------+------------+--------+
|      1 | 2023-01-10 00:02:06 |          1 |      3 |
|      2 | 2023-01-10 00:02:28 |          2 |      3 |
|      3 | 2023-01-10 00:21:13 |          3 |      3 |
|      4 | 2023-01-10 00:24:49 |          3 |      3 |
|      5 | 2023-01-10 00:30:58 |          1 |      1 |
+--------+---------------------+------------+--------+

Я сделал это

MariaDB [sae]> SELECT @rownum:=@rownum+1 as 'Classement', t.alias, SUM(a.point) as total_points FROM activity a INNER JOIN feed f ON a.id = f.ActivityId INNER JOIN team t ON f.TeamId = t.id JOIN (SELECT @rownum:=0) r GROUP BY t.alias ORDER BY total_points DESC, Classement DESC;
+------------+-------+--------------+
| Classement | alias | total_points |
+------------+-------+--------------+
|          2 | test  |          540 |
|          1 | login |            0 |
+------------+-------+--------------+

Здесь команда с наибольшим количеством очков содержит рейтинг 2 вместо одного, и если я сортирую по рейтингу ASC, это ничего не меняет.

Я желаю этого:

+------------+-------+--------------+
| Classement | alias | total_points |
+------------+-------+--------------+
|          1 | test  |          540 |
|          2 | login |            0 |
+------------+-------+--------------+

У вас есть идеи, как увеличить это целое число «назад»?

Попробуйте без ORDER BY Classement DESC

— 10.01.2023 11:59

Какая у вас версия mariadb?

— 10.01.2023 11:59

mysql sql mariadb

10.01.2023 11:51

Документирование API с помощью Swagger на Springboot

В предыдущей статье мы уже узнали, как создать Rest API с помощью Springboot и MySql .

Роли и разрешения пользователей без пакета Laravel 9

Этот пост изначально был опубликован на techsolutionstuff.com .

Как установить LAMP Stack - Security 5/5 на виртуальную машину Azure Linux VM

В предыдущей статье мы завершили установку базы данных, для тех, кто не знает.

Доступ AWS Java Lambda к экземпляру AWS RDS MySQL с помощью CDK

В этой статье мы рассмотрим, как включить доступ Java Lambda к экземпляру AWS RDS MySQL.

Как установить LAMP Stack 1/2 на Azure Linux VM

В дополнение к нашему предыдущему сообщению о намерении Azure прекратить поддержку Azure Database для MySQL в качестве единого сервера после 16...

Перейти к ответу Данный вопрос помечен как решенный

Ответы 3

Используя ту же логику, что и ваша, вы можете сделать это следующим образом:

select @rownum:=@rownum+1 as 'Classement', s.*
from (
    SELECT t.alias, SUM(a.point) as total_points 
    FROM activity a 
    INNER JOIN feed f ON a.id = f.ActivityId 
    INNER JOIN team t ON f.TeamId = t.id 
    JOIN (SELECT @rownum:=0) r 
    GROUP BY t.alias 
    ORDER BY total_points DESC
) as s;

Проверьте это здесь: https://dbfiddle.uk/TEz3UT97

Работает на mysql и mariadb

Этот оператор SQL выдаст неправильные результаты, если две или более команд наберут одинаковые баллы. Если test и test2 набрали 540 баллов, они оба должны иметь Classement=1, логин должен иметь Classement=3

— 10.01.2023 13:36

Я согласен с ответом @GeorgRichter. RANK() должно быть правильным решением.

— 10.01.2023 17:20

Спасибо @GeorgRichter и хобби_программисту, я добавил запрос для обработки случая, когда есть команды с одинаковым счетом.

— 10.01.2023 22:05

10.01.2023 12:09

Если вы не используете устаревшую версию MariaDB, вам следует использовать WINDOW-функцию RANK() вместо работы с пользовательскими переменными.

Работа с приращением пользовательской переменной возвращает то же значение, что и ROW_NUMBER(), но это неверно, поскольку команды с одинаковым количеством очков должны получить одинаковый рейтинг.

SELECT RANK() OVER (ORDER BY subq.total_points DESC) AS 'Classement', 
   subq.* FROM (
   SELECT team.alias, SUM(activity.point) AS total_points  FROM activity 
     JOIN feed ON activity.id = feed.ActivityId 
     JOIN team ON feed.TeamId = team.id  GROUP BY team.alias ) AS subq

10.01.2023 13:33

Ответ принят как подходящий

Это будет работать в случае, если две или более команд имеют одинаковый счет. оба они будут иметь одинаковый рейтинг:

Это совместимо со всеми версиями mysql и mariadb.

select @rank := CASE
    WHEN @totalval = total_points THEN @rank
    WHEN (@totalval := total_points) IS NOT NULL THEN @rank + 1
    WHEN (@totalval := total_points) IS NOT NULL THEN 1
END AS rank,
s.*
from (  
  SELECT t.alias, SUM(a.point) as total_points 
  FROM activity a 
  INNER JOIN feed f ON a.id = f.ActivityId 
  INNER JOIN team t ON f.TeamId = t.id 
  JOIN (SELECT @rank:=0, @totalval := 0) r 
  GROUP BY t.alias 
  ORDER BY total_points DESC
) as s;

Проверьте это здесь: https://dbfiddle.uk/7lKLu4Pw

10.01.2023 22:01