具有组内存的每个组的MySQL行号

Question

我正在用Java和MySQL做一个运行程序。因此，跑步者可以在比赛结束后统计谁是第一名。跑步者也在一个类别下比赛。因此，在每个类别下也都有赢家。所有数据都在MySQL中的不同表中，当比赛结束时，查询必须按类别显示表位置，这取决于它们完成的速度有多快。

我在MySQL中有这些表：

events (id, name, isActive)
runners (id, name, sex)
category (id, name, isActive)
inscriptions (id, event_id, runner_id, category, number_assigned, isActive)
race (id, inscription_id, start, end)

我有一个查询，一旦比赛结束，它就会告诉我表的位置。

SELECT  category.`name` AS `CATEGORÍA`, inscriptions.number_assigned AS `NÚMERO`, runners.name AS `COMPETIDOR`, runners.sex AS `SEXO`, races.`start` AS `INICIO`, races.`end` AS `FIN`, SEC_TO_TIME(UNIX_TIMESTAMP(races.`end`) - UNIX_TIMESTAMP(races.`start`)) AS `TIEMPO`
    FROM `events` 
        INNER JOIN `inscriptions` ON inscriptions.event_id = `events`.id 
        INNER JOIN `races` ON races.inscription_id = inscriptions.id 
        INNER JOIN `category` ON inscriptions.category = category.id 
        INNER JOIN `runners` ON inscriptions.runner_id = runners.id
    WHERE `events`.id = 1 
        AND inscriptions.isFinished = 0 
        AND `events`.isActive = 1 
        AND `inscriptions`.isActive = 1 
        AND `category`.isActive = 1 
        AND `runners`.isActive = 1
    ORDER BY races.`end` IS NULL, SEC_TO_TIME(UNIX_TIMESTAMP(races.`end`) - UNIX_TIMESTAMP(races.`start`)) ASC;

第一个查询的原因是因为我需要表位置的信息。我在group子句中包含"race.end IS NULL“，因为我需要在表的末尾没有结束时间(没有完成比赛)的跑步者。

这给了我一些类似如下的结果：

​

​

好的。如果我尝试执行添加"row number“的常规过程，因为查询首先将number赋给行，然后在其中应用order子句，所以一切都变得混乱。

SELECT 
    @r := @r+1 AS `POSICION`,
    `category`.`name` AS `CATEGORÍA`,
    `inscriptions`.`number_assigned` AS `NÚMERO`,
    `runner`.`name` AS `COMPETIDOR`,
    `runners`.`sex` AS `SEXO`,
    `races`.`start` AS `INICIO`,
    `races`.`end` AS `FIN`,
    SEC_TO_TIME(UNIX_TIMESTAMP(`races`.`end`) - UNIX_TIMESTAMP(`races`.`start`)) AS `TIEMPO`
  FROM
    (SELECT @r:=0)y, `events`
    INNER JOIN `inscriptions` ON (`inscriptions`.`event_id` = `events`.`id`)
    INNER JOIN `races` ON (`races`.`inscription_id` = `inscriptions`.`id`)
    INNER JOIN `category` ON (`inscriptions`.`category` = `category`.`id`)
    INNER JOIN `runners` ON (`inscriptions`.`runner_id` = `runners`.`id`)
  WHERE
    `events`.`id` = 1 AND 
    `inscriptions`.`isFinished` = 0 AND 
    `events`.`isActive` = 1 AND 
    `inscriptions`.`isActive` = 1 AND 
    `category`.`isActive` = 1 AND 
    `runners`.`isActive` = 1 AND 
    `races`.`end` IS NOT NULL
  ORDER BY 
    `races`.`end` IS NULL, 
    SEC_TO_TIME(UNIX_TIMESTAMP(`races`.`end`) - UNIX_TIMESTAMP(`races`.`start`)) ASC;

到处都是乱七八糟的东西：

​

​

好的。在搜索了一段时间后，我执行了一个查询，以获得我的“表位置”，然后将其与另一个位置交叉，以放置到目前为止的行号。工作正常。

现在是类别问题。

当尝试用分类系统做同样的事情时，查询只统计连续序列的数字，没有记忆在分类中像"10行后面有另一个高手在4个位置，所以这需要是5“。更具体地说，请附上错误和应该错误的代码。

错误：

1   1   Especiales  Especiales  614 Alex Chancusi   M   2016-10-09 07:12:53 2016-10-09 07:32:16 00:19:23
2   1   Juvenil Juvenil 491 Anthony Recalde Carrillo    M   2016-10-09 07:12:53 2016-10-09 07:35:34 00:22:41
3   1   Master  Master  610 Marco Almache   M   2016-10-09 07:12:53 2016-10-09 07:35:50 00:22:57
4   1   Senior  Senior  632 Cristian Rafael Caizapanta  M   2016-10-09 07:12:53 2016-10-09 07:36:17 00:23:24
5   2   Senior  Senior  138 Dennys Morocho Guayanlema   M   2016-10-09 07:12:53 2016-10-09 07:37:00 00:24:07
6   1   Master  Master  591 Manuel Suntaxi  M   2016-10-09 07:12:53 2016-10-09 07:37:35 00:24:42
7   1   Senior  Senior  508 Jhon Robles M   2016-10-09 07:12:53 2016-10-09 07:38:44 00:25:51
8   2   Senior  Senior  536 Margaret Karic Zoroitich    F   2016-10-09 07:12:53 2016-10-09 07:38:53 00:26:00
9   3   Senior  Senior  538 Carlos Moreno   M   2016-10-09 07:12:53 2016-10-09 07:39:20 00:26:27
10  1   Master  Master  550 Luis Toaquiza   M   2016-10-09 07:12:53 2016-10-09 07:39:43 00:26:50

这是错误的，因为如果你在位置3看到的是第一个位置的"Master“，然后是位置6的另一个具有相同位置的"Master”，在10位置也是如此。

好：

1   1   Especiales  Especiales  614 Alex Chancusi   M   2016-10-09 07:12:53 2016-10-09 07:32:16 00:19:23
2   1   Juvenil Juvenil 491 Anthony Recalde Carrillo    M   2016-10-09 07:12:53 2016-10-09 07:35:34 00:22:41
3   1   Master  Master  610 Marco Almache   M   2016-10-09 07:12:53 2016-10-09 07:35:50 00:22:57
4   1   Senior  Senior  632 Cristian Rafael Caizapanta  M   2016-10-09 07:12:53 2016-10-09 07:36:17 00:23:24
5   2   Senior  Senior  138 Dennys Morocho Guayanlema   M   2016-10-09 07:12:53 2016-10-09 07:37:00 00:24:07
6   2   Master  Master  591 Manuel Suntaxi  M   2016-10-09 07:12:53 2016-10-09 07:37:35 00:24:42
7   3   Senior  Senior  508 Jhon Robles M   2016-10-09 07:12:53 2016-10-09 07:38:44 00:25:51
8   4   Senior  Senior  536 Margaret Karic Zoroitich    F   2016-10-09 07:12:53 2016-10-09 07:38:53 00:26:00
9   5   Senior  Senior  538 Carlos Moreno   M   2016-10-09 07:12:53 2016-10-09 07:39:20 00:26:27
10  3   Master  Master  550 Luis Toaquiza   M   2016-10-09 07:12:53 2016-10-09 07:39:43 00:26:50

诸若此类。总是有不同的类别名称，所以类别列表不是一个合适的解决方案。

用于此结果的最终查询为：

SELECT @posGeneral := CASE WHEN z.`FIN` IS NULL THEN 'Not finished yet' ELSE @posGeneral+1 END AS `POS GENERAL`, 
  @posCat := CASE WHEN z.`FIN` IS NULL THEN '-' ELSE CASE WHEN @cat = z.`CATEGORÍA` THEN @posCat + 1 ELSE 1 END END AS `POS CATEGORÍA`, 
  @cat := z.`CATEGORÍA` AS `CAT`,
  z.* FROM(
  SELECT 
    `category`.`name` AS `CATEGORÍA`,
    `inscriptions`.`number_assigned` AS `NÚMERO`,
    `runner`.`name` AS `COMPETIDOR`,
    `runners`.`sex` AS `SEXO`,
    `races`.`start` AS `INICIO`,
    `races`.`end` AS `FIN`,
    SEC_TO_TIME(UNIX_TIMESTAMP(`races`.`end`) - UNIX_TIMESTAMP(`races`.`start`)) AS `TIEMPO`
  FROM
    `events`
    INNER JOIN `inscriptions` ON (`inscriptions`.`event_id` = `events`.`id`)
    INNER JOIN `races` ON (`races`.`inscription_id` = `inscriptions`.`id`)
    INNER JOIN `category` ON (`inscriptions`.`category` = `category`.`id`)
    INNER JOIN `runners` ON (`inscriptions`.`runner_id` = `runners`.`id`)
  WHERE
    `events`.`id` = 1 AND 
    `inscriptions`.`isFinished` = 1 AND 
    `events`.`isActive` = 1 AND 
    `inscriptions`.`isActive` = 1 AND 
    `category`.`isActive` = 1 AND 
    `runners`.`isActive` = 1
  ORDER BY 
    `races`.`end` IS NULL, 
    SEC_TO_TIME(UNIX_TIMESTAMP(`races`.`end`) - UNIX_TIMESTAMP(`races`.`start`)) ASC
)z, (SELECT @posGeneral:=0, @posCat:=0)y;

Answer 1

不要紧，我只是找到了怎么做...

这与MySQL如何理解查询以及如何处理查询结果有关……

Jus使用了ORDER子句，并在查询z.，y.和x.*中添加了一个新级别

z.*为行号y.*为“类别”位置(按类别排序) x.*原始查询。

不管怎样，谢谢你。