mysql - 具有额外条件的 MAX

Question

我在 MYSQL 中处理的查询有以下部分。

SELECT
  MAX(CAST(MatchPlayerBatting.BatRuns AS SIGNED)) AS HighestScore
FROM
  MatchPlayerBatting

它返回正确的结果。然而，还有另一列我需要它来处理。

也就是说，如果它找到的最大值在“BatHowOut”中也有一个“not out”的值，它应该显示结果，例如 96* 而不是仅仅 96。

这是怎么做到的？

为了帮助使数据具体化，请考虑两种情况:

BatRuns   BatHowOut
    96    not out
    96    lbw

BatRuns   BatHowOut
    96    not out
   102    lbw

对于第一个数据，答案应该是'96*'；对于第二个，'102'。

Answer 1

您可以像这样使用自连接来实现此目的:

SELECT t1.ID
, CONCAT(t1.BatRuns, 
         CASE WHEN t1.BatHowOut = 'Not Out' THEN '*' ELSE '' END
        ) AS HighScore
  FROM MatchPlayerBatting t1
  JOIN
  (
      SELECT MAX(BatRuns) AS HighestScore
      FROM MatchPlayerBatting
  ) t2
  ON t1.BatRuns = t2.HighestScore

• 参见 this sample SQLFiddle with highest "Not Out"
• 参见 this another sample SQLFiddle with highest "Out"
• 参见 this another sample SQLFiddle with two highest scores