問題
在工作中常會遇到將數(shù)據(jù)分組排序的問題，如在考試成績中，找出每個班級的前五名等。 在等數(shù)據(jù)庫中可以使用 語句來解決，但在mysql中就比較麻煩了。這次翻譯的文章就是專門解決這個問題的
翻譯
在使用SQL的過程中，我們經(jīng)常遇到這樣一類問題：如何找出每個程序最近的日志條目？如何找出每個用戶的最高分？在每個分類中最受歡迎的商品是什么?通常這類“找出每個分組中最高分的條目”的問題可以使用相同的技術(shù)來解決。在這篇文章里我將介紹如何解決這類問題，而且會介紹如何找出最高的前幾名而不僅僅是第一名。
這篇文章會用到行數(shù)(row )，我在原來的文章MySQL-和 中已經(jīng)提到過如何為每個分組設(shè)置行數(shù)了。在這里我會使用與原來的文章中相同的表格，但會加入新的price 字段
1 +--------+------------+-------+
2 | type | | price |
3 +--------+------------+-------+
4 | apple | gala | 2.79 |
5 | apple | fuji | 0.24 |
6 | apple | | 2.87 |
7 | | | 3.59 |
8 | | navel | 9.36 |
9 | pear | | 6.05 |
10 | pear | | 2.14 |
11 | | bing | 2.55 |
12 | | | 6.33 |
13 +--------+------------+-------+
選擇每個分組中的最高分
這里我們要說的是如何找出每個程序最新的日志記錄或?qū)徍吮碇凶罱母禄蚱渌愃频呐判騿栴}。這類問題在IRC頻道和郵件列表中出現(xiàn)的越來越頻繁。我使用水果問題來作為示例，在示例中我們要選出每類水果中最便宜的一個，我們期望的結(jié)果如下
+--------+----------+-------+
| type | | price |
+--------+----------+-------+
| apple | fuji | 0.24 |
| | | 3.59 |
| pear | | 2.14 |
| | bing | 2.55 |
+--------+----------+-------+
這個問題有幾種解法，但基本上就是這兩步：找出最低的價格，然后找出和這個價格同一行的其他數(shù)據(jù)
其中一個常用的方法是使用自連接(self-join)，第一步根據(jù)type(apple, etc)進行分組，并找出每組中price的最小值
type, min(price) group ;+--------+----------+
| type | |
+--------+----------+
| apple | 0.24 |
| | 2.55 |
| | 3.59 |
| pear | 2.14 |
+--------+----------+
第二步是將剛剛結(jié)果與原來的表進行連接。既然剛剛給結(jié)果已經(jīng)被分組了，我們將剛剛的查詢語句作為子查詢以便于連接沒有被分組的原始表格。
.type, f., f.( type, min(price) group
)as x inner join as f on f.type = x.type and f.price =x.;+--------+----------+-------+
| type | | price |
+--------+----------+-------+
| apple | fuji | 0.24 |
| | bing | 2.55 |
| | | 3.59 |
| pear | | 2.14 |
+--------+----------+-------+
還可以使用相關(guān)子查詢( )的方式來解決。這種方法在不同的mysql優(yōu)化系統(tǒng)下，可能性能會有一點點下降，但這種方法會更直觀一些。
, , price = ( min(price) from as f where f.type =.type);+--------+----------+-------+
| type | | price |
+--------+----------+-------+
| apple | fuji | 0.24 |
| | | 3.59 |
| pear | | 2.14 |
| | bing | 2.55 |
+--------+----------+-------+
這兩種查詢在邏輯上是一樣的，他們性能也基本相同
找出每組中前N個值
這個問題會稍微復雜一些。我們可以使用聚集函數(shù)(MIN(), MAX()等等)來找一行，但是找前幾行不能直接使用這些函數(shù)，因為它們都只返回一個值。但這個問題還是可以解決的。
這次我們找出每個類型(type)中最便宜的前兩種水果mysql分組排序取前三條，首先我們嘗試
, , price = ( min(price) from as f where f.type =.type)or price = ( min(price) from as f where f.type =. price > ( min(price) from as f2 where f2.type =.type));+--------+----------+-------+
| type | | price |
+--------+----------+-------+
| apple | gala | 2.79 |
| apple | fuji | 0.24 |
| | | 3.59 |
| | navel | 9.36 |
| pear | | 6.05 |
| pear | | 2.14 |
| | bing | 2.55 |
| | | 6.33 |
+--------+----------+-------+
是的，我們可以寫成自連接(self-join)的形式，但是仍不夠好(我將這個練習留給讀者)。這種方式在N變大(前三名，前4名)的時候性能會越來越差。我們可以使用其他的表現(xiàn)形式編寫這個查詢mysql分組排序取前三條，但是它們都不夠好，它們都相當?shù)谋恐睾托实拖隆?譯者注：這種方式獲取的結(jié)果時，如果第N個排名是重復的時候最后選擇的結(jié)果會超過N，比如上面例子還有一個apple價格也是0.24，那最后的結(jié)果就會有3個apple)
我們有一種稍好的方式，在每個種類中選擇不超過該種類第二便宜的水果
, , ( count(*) from f.type = .type and f.price