带有附加条件的MAX [英] MAX with extra criteria

问题描述

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

I have the following part of a query I'm working on in MYSQL.

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

它返回正确的结果.但是，还有另一列我需要它来解决.

It returns the correct result. However there is another column I need it to work off.

也就是说，如果发现的最大值在"BatHowOut"中的值也为"not out"，则结果应显示为例如96 *而不是96.

That is if the maximum value it finds also has a value of "not out" within "BatHowOut", it should show the result as for example 96* rather than just 96.

这怎么办?

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

To help make the data concrete, consider two cases:

BatRuns   BatHowOut
    96    not out
    96    lbw

BatRuns   BatHowOut
    96    not out
   102    lbw

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

For the first data, the answer should be '96*'; for the second, '102'.

推荐答案

如何按降序排列分数并仅选择第一条记录?

How about ordering the scores in descending order and selecting only the first record?

select concat(BatRuns , case when BatHowOut = 'not out' then '*' else '' end)
  from mytable
order by cast(BatRuns as signed) desc,
        (case when BatHowOut = 'not out' then 1 else 2 end)
limit 1;

示例此处.

如果您想为每个玩家找到最高的分数，这是一个可能并不优雅但很有效的解决方案.

If you want to find highest score score for each player, here is a solution that may not be elegant, but quite effective.

select PlayerID,
       case when runs != round(runs)
                 then concat(round(runs),'*')
            else
                 round(runs)
       end highest_score
  from (select PlayerID,
               max(cast(BatRuns as decimal) + 
                   case when BatHowOut = 'not out' then 0.1 else 0 end
                  ) runs
          from MatchPlayerBatting
         group by PlayerID) max_runs;

这利用了一个事实，即游程永远不能是分数，只能是整数.当最高分并列，其中一个不败时， 在不败得分上加0.1将使它成为最高分.以后可以将其删除并与*串联.

This takes advantage of the fact that, runs can never be fractions, only whole numbers. When there is a tie for highest score and one of them is unbeaten, adding 0.1 to the unbeaten score will make it the highest. This can be later removed and concatenated with *.

示例此处.

这篇关于带有附加条件的MAX的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！