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




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

乍一看...

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

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

它從來沒有那麼簡單,是嗎?

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

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

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

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

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

在這種方法中,您首先在子查詢中找到group-identifier, max-value-in-group (上面已解決)。 然後,將您的表加入到子查詢中,使用group-identifiermax-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-identifiermax-value-in-group ,那麼兩個行都將在兩種方法的結果中出現。

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

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

我有這張表格(這裡是簡體版):

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

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

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

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

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


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);




greatest-n-per-group