MySQL SELECT唯一列，其中其他列最大 [英] MySQL SELECT unique column where other column is max

问题描述

我有这样的桌子

  id     |     serial_num     |      version     | .....
  1      |         1          |          1       | .....
  2      |         2          |          1       | .....
  3      |         2          |          2       | .....
  4      |         3          |          1       | .....
  5      |         4          |          1       | .....
  6      |         5          |          1       | .....
  7      |         5          |          2       | .....
  8      |         5          |          3       | .....

现在我要选择的是获取具有最高版本和唯一的serialn_num ...的行...

Now what I want to select is to get rows with max version and unique serialn_num ...

结果将是:

  id     |     serial_num     |      version     | .....
  1      |         1          |          1       | .....
  3      |         2          |          2       | .....
  4      |         3          |          1       | .....
  5      |         4          |          1       | .....
  8      |         5          |          3       | .....

我的SQL有点复杂，这就是为什么我不通过使用MAX()解决问题的原因...我几乎没有左联接等...

My SQL is a bit more complicated and that is why I don't solve the problem by using MAX()... I have few left joins etc ...

有什么想法吗?

最诚挚的问候，谢谢您的宝贵时间！

Best regards and thank you for your time!

推荐答案

尝试一下:

SELECT yourtable.*
FROM yourtable
WHERE (serial_num, version) in (select serial_num, max(version)
                                from yourtable
                                group by serial_num)

子查询将返回serial_num的最大版本，因此它将返回serial_num具有最大值的所有行.参见此小提琴.

Subquery will return the maximum version for serial_num, so this will return all rows where serial_num has the maximum value. See this fiddle.

这篇关于MySQL SELECT唯一列，其中其他列最大的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！