java - 每个组的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)

我有这个查询，可以在比赛结束后为我提供牌 table 位置。

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;

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

这给了我这样的结果:

好的。如果我尝试执行添加“行号”的正常过程，一切都会变得一团糟，因为查询首先为该行分配编号，然后在其中应用 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 如何理解查询以及如何处理结果查询有关...

稍微玩一下 ORDER 子句并在查询 z.、y. 和 x.* 中添加一个新级别

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

还是谢谢你