mysql - where最大值 - sql最大值條件




SQL只選擇列上具有最大值的行 (20)

我有這張表格（這裡是簡體版）： 

+------+-------+--------------------------------------+
| id   | rev   | content                              |
+------+-------+--------------------------------------+
| 1    | 1     | ...                                  |
| 2    | 1     | ...                                  |
| 1    | 2     | ...                                  |
| 1    | 3     | ...                                  |
+------+-------+--------------------------------------+

我如何為每個ID選擇一行而只選擇最大的轉速？
通過上述數據，結果應該包含兩行： [1, 3, ...]和[2, 1, ..] 。 我正在使用MySQL 。

目前我在while循環中使用檢查來檢測並覆蓋結果集中的舊版本。 但是，這是實現結果的唯一方法嗎？ 沒有SQL解決方案嗎？

更新
正如答案所示，有一個SQL解決方案， 這裡是一個sqlfiddle演示 。

更新2
我注意到在添加上面的sqlfiddle之後 ，問題被提高的速率已經超過了答案的滿意率。 這並非意圖！ 小提琴基於答案，特別是被接受的答案。

乍一看...

所有你需要的是一個帶有MAX聚合函數的GROUP BY子句： 

SELECT id, MAX(rev)
FROM YourTable
GROUP BY id

它從來沒有那麼簡單，是嗎？

我只是注意到你也需要content列。

這是SQL中的一個非常常見的問題：根據某個組標識符在列中查找具有某個最大值的行的整個數據。 在我的職業生涯中，我聽到了很多。 事實上，這是我在當前工作的技術面試中回答的問題之一。

實際上，社區已經創建了一個標籤來處理類似這樣的問題： greatest-n-per-group 。

基本上，你有兩種方法來解決這個問題：

加入簡單的group-identifier, max-value-in-group子查詢

在這種方法中，您首先在子查詢中找到group-identifier, max-value-in-group （上面已解決）。 然後，將您的表加入到子查詢中，使用group-identifier和max-value-in-group上的等式： 

SELECT a.id, a.rev, a.contents
FROM YourTable a
INNER JOIN (
    SELECT id, MAX(rev) rev
    FROM YourTable
    GROUP BY id
) b ON a.id = b.id AND a.rev = b.rev

左連接與自我，調整連接條件和過濾器

在這種方法中，您將自己與表連接起來。 平等，當然，在group-identifier 。 然後，2個聰明的動作：

  1. 第二個連接條件是左側值小於右側值
  2. 當您執行第1步時，實際上具有最大值的行將在右側具有NULL （這是一個LEFT JOIN ，請記住？）。 然後，我們過濾連接的結果，只顯示右側為NULL的行。

所以你最終得到： 

SELECT a.*
FROM YourTable a
LEFT OUTER JOIN YourTable b
    ON a.id = b.id AND a.rev < b.rev
WHERE b.id IS NULL;

結論

兩種方法都帶來了完全相同的結果。

如果您有兩行group-identifier為max-value-in-group ，那麼兩個行都將在兩種方法的結果中出現。

這兩種方法都兼容SQL ANSI，因此，無論其“風味”如何，都可與您最喜愛的RDBMS一起使用。

兩種方法都對性能友好，但是您的里程可能會有所不同（RDBMS，數據庫結構，索引等）。 所以當你選擇一種方法， 基準 。 並確保你選擇對你最有意義的一個。

SELECT * FROM僱員，其中Employee.Salary（僱員組中僱員僱員通過Employe_id選擇max（薪水））ORDER BY Employee.Salary

做這項工作的另一種方式是在OVER PARTITION子句中使用MAX（）分析函數 

SELECT t.*
  FROM
    (
    SELECT id
          ,rev
          ,contents
          ,MAX(rev) OVER (PARTITION BY id) as max_rev
      FROM YourTable
    ) t
  WHERE t.rev = t.max_rev

在這篇文章中已經記錄的另一個OVER PARTITION解決方案是 

SELECT t.*
  FROM
    (
    SELECT id
          ,rev
          ,contents
          ,ROW_NUMBER() OVER (PARTITION BY id ORDER BY rev DESC) rank
      FROM YourTable
    ) t
  WHERE t.rank = 1

這2個SELECT在Oracle 10g上運行良好。

像這樣？ 

SELECT yourtable.id, rev, content
FROM yourtable
INNER JOIN (
    SELECT id, max(rev) as maxrev FROM yourtable
    WHERE yourtable
    GROUP BY id
) AS child ON (yourtable.id = child.id) AND (yourtable.rev = maxrev)

如果在select語句中有許多字段，並且您希望通過優化代碼獲得所有這些字段的最新值： 

select * from
(select * from table_name
order by id,rev desc) temp
group by id

如果有人正在尋找Linq verson，這似乎對我有用： 

public static IQueryable<BlockVersion> LatestVersionsPerBlock(this IQueryable<BlockVersion> blockVersions)
{
    var max_version_per_id = blockVersions.GroupBy(v => v.BlockId)
        .Select( v => new { BlockId = v.Key, MaxVersion = v.Max(x => x.Version) } );    

    return blockVersions.Where( v => max_version_per_id.Any(x => x.BlockId == v.BlockId && x.MaxVersion == v.Version) );
}

我喜歡使用基於NOT EXIST的解決方案來解決這個問題： 

SELECT id, rev
FROM YourTable t
WHERE NOT EXISTS (
   SELECT * FROM YourTable t WHERE t.id = id AND rev > t.rev
)

我喜歡通過按列排列記錄來完成此操作。 在這種情況下，按id分組排列rev值。 那些rev較高的人排名會較低。 所以最高rev將有1的排名。 

select id, rev, content
from
 (select
    @rowNum := if(@prevValue = id, @rowNum+1, 1) as row_num,
    id, rev, content,
    @prevValue := id
  from
   (select id, rev, content from YOURTABLE order by id asc, rev desc) TEMP,
   (select @rowNum := 1 from DUAL) X,
   (select @prevValue := -1 from DUAL) Y) TEMP
where row_num = 1;

不確定引入變量是否會使整個事情變得更慢。 但至少我不會查詢YOURTABLE兩次。

我幾乎沒有見過的第三種解決方案是具體的MySQL，如下所示： 

SELECT id, MAX(rev) AS rev
 , 0+SUBSTRING_INDEX(GROUP_CONCAT(numeric_content ORDER BY rev DESC), ',', 1) AS numeric_content
FROM t1
GROUP BY id

是的，它看起來很糟糕（轉換為字符串和後面等），但根據我的經驗，它通常比其他解決方案更快。 也許這只是為了我的用例，但我已經在具有數百萬條記錄和許多獨特ID的表上使用它。 也許是因為MySQL在優化其他解決方案方面相當糟糕（至少在我提出這個解決方案的5.0天內）。

一個重要的事情是GROUP_CONCAT具有它可以建立的字符串的最大長度。 您可能想通過設置group_concat_max_len變量來提高此限制。 請記住，如果您有大量的行，這將成為縮放的限制。

無論如何，如果您的內容字段已經是文字，上述內容不會直接工作。 在這種情況下，您可能想要使用不同的分隔符，例如\ 0。 你也會更快地遇到group_concat_max_len限制。

我會用這個： 

select t.*
from test as t
join
   (select max(rev) as rev
    from test
    group by id) as o
on o.rev = t.rev

子查詢SELECT可能不太有效，但在JOIN子句中似乎可用。 我不是優化查詢的專家，但我已經在MySQL，PostgreSQL和FireBird上嘗試過了，它的工作非常好。

您可以在多個連接和WHERE子句中使用此模式。 這是我的工作示例（與表“firmy”一樣解決您的問題）： 

select *
from platnosci as p
join firmy as f
on p.id_rel_firmy = f.id_rel
join (select max(id_obj) as id_obj
      from firmy
      group by id_rel) as o
on o.id_obj = f.id_obj and p.od > '2014-03-01'

它在有十幾歲和十幾歲的桌子上被詢問，而在真正不太強的機器上，它需要少於0.01秒。

我不會使用IN子句（就像上面提到的那樣）。 IN被用來與簡短的常量列表一起使用，而不是構建在子查詢上的查詢過濾器。 這是因為IN中的子查詢是針對每個掃描的記錄執行的，這可以使查詢花費很長時間。

我用下面的方法來解決我自己的問題。 我首先創建了一個臨時表，並為每個唯一ID插入最大轉速值。 

CREATE TABLE #temp1
(
    id varchar(20)
    , rev int
)
INSERT INTO #temp1
SELECT a.id, MAX(a.rev) as rev
FROM 
    (
        SELECT id, content, SUM(rev) as rev
        FROM YourTable
        GROUP BY id, content
    ) as a 
GROUP BY a.id
ORDER BY a.id

然後，我將這些最大值（＃temp1）加入了所有可能的id /內容組合。 通過這樣做，我自然會過濾掉非最大的id /內容組合，並且剩下每個組合的最大轉速值。 

SELECT a.id, a.rev, content
FROM #temp1 as a
LEFT JOIN
    (
        SELECT id, content, SUM(rev) as rev
        FROM YourTable
        GROUP BY id, content
    ) as b on a.id = b.id and a.rev = b.rev
GROUP BY a.id, a.rev, b.content
ORDER BY a.id

我的首選是盡可能少使用代碼...

你可以使用IN做到這一點： 

SELECT * 
FROM t1 WHERE (id,rev) IN 
( SELECT id, MAX(rev)
  FROM t1
  GROUP BY id
)

在我看來，它並不復雜......更易於閱讀和維護。

按照相反的順序對rev字段進行排序，然後按照id進行分組，這樣每個分組的第一行就是具有最高rev值的分組。 

SELECT * FROM (SELECT * FROM table1 ORDER BY id, rev DESC) X GROUP BY X.id;

使用以下數據在http://sqlfiddle.com/進行測試 

CREATE TABLE table1
    (`id` int, `rev` int, `content` varchar(11));

INSERT INTO table1
    (`id`, `rev`, `content`)
VALUES
    (1, 1, 'One-One'),
    (1, 2, 'One-Two'),
    (2, 1, 'Two-One'),
    (2, 2, 'Two-Two'),
    (3, 2, 'Three-Two'),
    (3, 1, 'Three-One'),
    (3, 3, 'Three-Three')
;

這在MySql 5.5和5.6中給出了以下結果 

id  rev content
1   2   One-Two
2   2   Two-Two
3   3   Three-Two

由於這是關於這個問題的最普遍的問題，我將在這裡重新發布另一個答案：

看起來有更簡單的方法來做到這一點（但只在MySQL中 ）： 

select *
from (select * from mytable order by id, rev desc ) x
group by id

在這個問題上 ， 請給出用戶波希米亞人的回答，以便為這個問題提供這樣一個簡潔而優雅的答案。

編輯：雖然這種解決方案適用於很多人，但長期來看它可能並不穩定，因為MySQL不保證GROUP BY語句將為不在GROUP BY列表中的列返回有意義的值。 因此，使用此解決方案需要您自擔風險

這些答案都不適合我。

這對我來說很有用。 

with score as (select max(score_up) from history)
select history.* from score, history where history.score_up = score.max

這個怎麼樣： 

select all_fields.*  
from  (select id, MAX(rev) from yourtable group by id) as max_recs  
left outer join yourtable as all_fields  
on max_recs.id = all_fields.id

這是一個很好的做法

使用以下代碼： 

with temp as  ( 
select count(field1) as summ , field1
from table_name
group by field1 )
select * from temp where summ = (select max(summ) from temp)

這是另一種解決方案，只有使用具有該字段最大值的字段才能檢索記錄。 這適用於我工作的平台SQL400。 在這個例子中，字段FIELD5中具有最大值的記錄將由以下SQL語句檢索。 

SELECT A.KEYFIELD1, A.KEYFIELD2, A.FIELD3, A.FIELD4, A.FIELD5
  FROM MYFILE A
 WHERE RRN(A) IN
   (SELECT RRN(B) 
      FROM MYFILE B
     WHERE B.KEYFIELD1 = A.KEYFIELD1 AND B.KEYFIELD2 = A.KEYFIELD2
     ORDER BY B.FIELD5 DESC
     FETCH FIRST ROW ONLY)

SELECT * FROM t1 ORDER BY rev DESC LIMIT 1;

select * from yourtable
group by id
having rev=max(rev);



where最大值   sql最大值條件   sql只顯示最大值   sql取最大值的行   sql不同欄位最大值   mysql   sql   aggregate-functions   greatest-n-per-group  


greatest-n-per-group