MySQL Select 语句返回并不总是在同一列中的数据 - mysql select statement to return data that isn't always in the same column.

mysql select statement to return data that isn't always in the same column.

我有一个包含比赛名称和结果的Db。我正在尝试编写一个 sql 语句，该语句将确定所有特定名称的结果。问题是，根据比赛的地点，本地或公路，结果在不同的列中。例如

    ____________________________________________________________________
    | home Runner | Road runner| road Outcome | home Outcome |   ID     |
    --------------------------------------------------------------------
    |    Jerry    |    Brian   |     323      |     350      |    1     |
    --------------------------------------------------------------------
    |   Brian     |    Jerry   |     259      |     265      |    2     |

正如你所看到的，如果我想为布莱恩拉出结果，那就变得很困难了，因为他的结果可以在道路和家庭列之间切换。我以为我可以存储一个数组，其中包含与 brian 匹配的所有 id，然后根据 brian 所在的列（家、路）以某种方式返回结果，到目前为止我已经完成了以下操作，但卡在底部

    $getRunner = "select id from $db where homeRunner = brian union select id from $db where roadRunner = Brian" order by id asc;
    $result=mysql_query($getRunner);
    $id=array(); // this is to hold all the Id's that match
    while($row = mysql_fetch_array($result)){
        $id[]=$row['id'];
    }
    foreach ($id as $element){
        $getOutcome = "select roadOutcome from $db where roadRunner = brian and id =$element"; 
        $result2 = mysql_query($getOutcome);
        while($row2 = mysql_fetch_array($result2)){
            echo $row2['roadOutcome'];
            echo '<br />';
        }

我知道这里有很多问题。如果可能的话，我想合并查询，更不用说让它工作了。我怎么能说"如果 homeRunner = brian，然后选择 homeResult，否则选择 roadResult，其中 id = $element"？

最终，我试图在数组中返回答案，以便我可以绘制它。我正在用PHP写这个。感谢您的帮助！

SELECT 
  CASE 
    WHEN HomeRunner = 'Brian' THEN 'Home'
    ELSE 'Road'
  END OutcomeType,
  CASE 
    WHEN HomeRunner = 'Brian' THEN HomeOutcome
    ELSE RoadOutcome 
  END Outcome
FROM $db
WHERE HomeRunner = 'Brian' OR RoadRunner = 'Brian'

您可以在单个查询中实现这一点，如下所示：

 SELECT IF(homeRunner = 'Brian', homeOutcome, road_Outcum) AS outcum
 FROM table_name
 WHERE (homeRunner = 'Brian' OR roadRunner = 'Brian');

试试这个：

$getOutcome = "SELECT CASE 'BRIAN' 
               WHEN homeRunner THEN homeOutcome
               WHEN roadRunner THEN roadOutcome
               END FROM $db WHERE id =$element";
$result2 = mysql_query($getOutcome);
$outcome = mysql_fetch_row($getOutcome);
echo $outcome[0]; // Final Output!