mysql - 从另一个表中选择具有两个不同外键的同一列

Question

我觉得我离我的解决方案很近了，但还不够。我正在尝试选择 testRun.build 两次，

第一次基于当前运行 (Results.build_fk) 和基于测试通过的最后一次运行 (Results.lastPass)。

这是我到目前为止的 Select 语句，我觉得它应该可以工作 ( because of this answer )，但它告诉我“子查询返回超过 1 行”。

SELECT Results.build_fk, Results.testCaseID, Results.pass, Results.time, 

( SELECT testRun.build

  FROM testRun 

 INNER JOIN Results r1 ON r1.build_fk = testRun.runID 

) AS Last_build, 

( SELECT testRun.build

  FROM testRun 

  INNER JOIN Results r2 ON r2.lastPass = testRun.runID
) 
  AS Current_Build

 FROM Results 

  INNER JOIN testCases AS t1 ON t1.testCaseID = Results.testCaseID

  ORDER BY build_fk DESC, testCaseID ASC;

这里是示例表，带有示例数据:

Results
(`build_fk`, `testCaseID`, `pass`, `time`, `lastPass`)
(1132, 200, 0, {some timestamp}, 1132)
(1133, 200, 0, {some timestamp}, 1132)
(1134, 200, 1, {some timestamp}, 1134)
(1132, 210, 0, {some timestamp}, 1132)
(1133, 210, 0, {some timestamp}, 1132)
(1134, 210, 1, {some timestamp}, 1134)

testRun
(`runID`, `build`)
(1132, 'build-1.0')
(1133, 'build-1.1')
(1134, 'build-1.2')

抱歉没有 SQLFiddle，我无法正确创建表。

Answer 1

我认为子查询中的额外连接是不必要的:

SELECT r.build_fk, r.testCaseID, r.pass, r.time, 
       (SELECT tr.build
        FROM testRun tr
        WHERE r.build_fk = tr.runID 
       ) AS Last_build, 
       (SELECT tr.build
        FROM testRun tr
        WHERE r.lastPass = tr.runID
       ) AS Current_Build
FROM Results  r INNER JOIN
     testCases t1
     ON t1.testCaseID = r.testCaseID
ORDER BY build_fk DESC, testCaseID ASC;

您希望子查询引用外部查询(在此版本中使用 r 表别名)。这称为相关子查询。