mysql - 选择行号等于其他表中某个值的行

Question

我有两个表:游戏和任务

游戏看起来是这样的:

| step | manualTaskCounter | autoTaskCounter | (and other)
----------------------------------------------------------
|  1   |      3            | 1               | ...
----------------------------------------------------------

任务看起来像这样:

| id | taskType | taskContent |
-------------------------------
|  1 |    M     | abc         |
|  2 |    M     | cde         |
|  3 |    A     | efg         |
|  4 |    M     | jpq         |

由于任务包含手动(M 任务类型)和自动 (A) 任务，我想选择。我的 API 包含两个变量:mTaskCounter 和 aTaskCounter。例如，如果 mTaskCounter = 3 我想从任务中选择类型为 manualTask​​ 的第 3 行。因为它实际上是 id = 4 的行，所以我不能在 WHERE 子句中使用 id。

我已经实现的是:

SELECT
    id,
    taskType,
    taskContent,
    (@row:=@row + 1) as rowNumber,
    g.manualTaskCounter as mTaskCounter
FROM
    tasks t,
    (SELECT @ROW:=0) AS r,
    (SELECT manualTaskCounter FROM game) AS g
WHERE
    g.manualTaskCounter = rowNumber

这表示“where 子句中的未知列‘rowNumber’”

我还尝试使用 LEFT JOIN:

SELECT
    id,
    taskType,
    taskContent,
    (@row:=@row + 1) as rowNumber,
    g.manualTaskCounter as mTaskCounter
FROM
    tasks t,
    (SELECT @ROW:=0) AS r
LEFT JOIN
    `game` g ON g.manualTaskCounter = rowNumber

同样的结果。自从我每天使用 mysql 以来已经有一段时间了，并且不知道如何修复它。我还想制作两个表 - manualTask​​s 和 autoTasks 而不是任务，因此它可以通过常见的 select taskContent from autoTasks a LEFT JOIN game ON a.id = game.autoTaskCounter

解决问题

Answer 1

为了实现您的目标，首先您需要为手动 和自动 任务创建派生表。接下来的查询将使这些表也加起来行号:

包含手动任务的表格

SELECT
    t.id,
    t.taskType,
    t.taskContent,
    (@row_num := @row_num + 1) AS rowNum
FROM
    tasks AS t
CROSS JOIN
    (SELECT @row_num := 0) AS r
WHERE
    taskType = 'M'

带有自动任务的表格

SELECT
    t.id,
    t.taskType,
    t.taskContent,
    (@row_num := @row_num + 1) AS rowNum
FROM
    tasks AS t
CROSS JOIN
    (SELECT @row_num := 0) AS r
WHERE
    taskType = 'A'

现在，您需要做的就是在适当的列上将这些派生表与 game 表连接起来:

使用 manualTask​​Counter 字段选择手动任务编号 X

SELECT
    mTasks.*
FROM
    game AS g
INNER JOIN
    ( SELECT
          t.id,
          t.taskType,
          t.taskContent,
          (@row_num := @row_num + 1) AS rowNum
      FROM
          tasks AS t
      CROSS JOIN
          (SELECT @row_num := 0) AS r
      WHERE
           taskType = 'M' ) AS mTasks ON mTasks.rowNum = g.manualTaskCounter

使用 autoTaskCounter 字段选择自动任务编号 X

SELECT
    aTasks.*
FROM
    game AS g
INNER JOIN
    ( SELECT
          t.id,
          t.taskType,
          t.taskContent,
          (@row_num := @row_num + 1) AS rowNum
      FROM
          tasks AS t
      CROSS JOIN
          (SELECT @row_num := 0) AS r
      WHERE
           taskType = 'A' ) AS aTasks ON aTasks.rowNum = g.autoTaskCounter

查看下一个在线示例:

DB Fiddle Example