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-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步時,實際上具有最大值的行將在右側具有
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,數據庫結構,索引等)。 所以當你選擇一種方法, 基準 。 並確保你選擇對你最有意義的一個。
我有這張表格(這裡是簡體版):
+------+-------+--------------------------------------+
| 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
這些答案都不適合我。
這對我來說很有用。
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);