在mysql查询中找到多行的最大列集 [英] find maximum of set of columns for multiple rows in mysql query

问题描述

我有一个包含以下字段和数据的表.

I have a table with the following fields and data.

RefNo Year Month Code
1     2010  7     A
2     2009  10    B
3     2010  8     A
4     2010  5     B

由此，我必须找出特定代码的最大值(年，月). 结果必须是

From this i've to find out the maximum(year, month) for a particular code. The result must be

3   2010  8 A
1   2010  7 B

请帮助我解决这个问题.

Please help me solve this.

推荐答案

SELECT
  *
FROM
(
  SELECT
    ROW_NUMBER() OVER (PARTITION BY Code ORDER BY Year DESC, Month DESC) AS row_number,
    *
  FROM
    myTable
)
  AS orderedTable
WHERE
  row_number = 1

编辑，现在是在MySQL中工作的版本...

EDIT And now for a version that WORKS in MySQL...

SELECT
  *
FROM
  myTable
WHERE
  Year * 12 + Month = (SELECT MAX(Year * 12 + Month) FROM myTable AS lookup WHERE Code = myTable.Code)

编辑访问SQL后，可以确认这是更快的操作...

EDIT After access to SQL, can confirm this is faster...

WHERE
    Year  = (SELECT MAX(Year)  FROM myTable AS lookup WHERE Code = myTable.Code)
AND Month = (SELECT MAX(Month) FROM myTable AS lookup WHERE Code = myTable.Code AND Year = myTable.Year)

这篇关于在mysql查询中找到多行的最大列集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！