mysql rows only,MySQL：選擇N行，但在一列中只有唯一值

Given this data set:

鑒於此數據集：

ID Name City Birthyear

1 Egon Spengler New York 1957

2 Mac Taylor New York 1955

3 Sarah Connor Los Angeles 1959

4 Jean-Luc Picard La Barre 2305

5 Ellen Ripley Nostromo 2092

6 James T. Kirk Riverside 2233

7 Henry Jones Chicago 1899

I need to find the 3 oldest persons, but only one of every city.

我需要找到3個最老的人，但每個城市只有一個。

If it would just be the three oldest, it would be...

如果它只是三個最古老的，它將是......

Henry Jones / Chicago

亨利瓊斯/芝加哥

Mac Taylor / New York

麥克泰勒/紐約

Egon Spengler / New York

Egon Spengler /紐約

However since both Egon Spengler and Mac Taylor are located in New York, Egon Spengler would drop out and the next one (Sarah Connor / Los Angeles) would come in instead.

然而，由於Egon Spengler和Mac Taylor都位於紐約，因此Egon Spengler將退出，而下一個(Sarah Connor /洛杉磯)將會進入。

Any elegant solutions?

優雅的解決方案？

Update:

更新：

Currently a variation of PConroy is the best/fastest solution:

目前，PConroy的變體是最好/最快的解決方案：

SELECT P.*, COUNT(*) AS ct

FROM people P

JOIN (SELECT MIN(Birthyear) AS Birthyear

FROM people

GROUP by City) P2 ON P2.Birthyear = P.Birthyear

GROUP BY P.City

ORDER BY P.Birthyear ASC

LIMIT 10;

His original query with "IN" is extremly slow with big datasets (aborted after 5 minutes), but moving the subquery to a JOIN will speed it up a lot. It took about 0.15 seconds for approx. 1 mio rows in my test environment. I have an index on "City, Birthyear" and a second one just on "Birthyear".

他使用“IN”的原始查詢對於大數據集來說極其緩慢(在5分鍾后中止)，但是將子查詢移動到JOIN會加快它的速度。約需0.15秒。我的測試環境中有1 mio行。我有一個關於“City，Birthyear”的索引和第二個關於“Birthyear”的索引。

Note: This is related to...

注意：這與...有關

5 个解决方案

#1

18

Probably not the most elegant of solutions, and the performance of IN may suffer on larger tables.

可能不是最優雅的解決方案，IN的性能可能會受到更大的表格的影響。

The nested query gets the minimum Birthyear for each city. Only records who have this Birthyear are matched in the outer query. Ordering by age then limiting to 3 results gets you the 3 oldest people who are also the oldest in their city (Egon Spengler drops out..)

嵌套查詢獲得每個城市的最小Birthyear。只有具有此Birthyear的記錄才會在外部查詢中匹配。按年齡排序然后限制為3個結果讓你成為他們城市中最老的3個最老的人(Egon Spengler退出..)

SELECT Name, City, Birthyear, COUNT(*) AS ct

FROM table

WHERE Birthyear IN (SELECT MIN(Birthyear)

FROM table

GROUP by City)

GROUP BY City

ORDER BY Birthyear DESC LIMIT 3;

+-----------------+-------------+------+----+

| name | city | year | ct |

+-----------------+-------------+------+----+

| Henry Jones | Chicago | 1899 | 1 |

| Mac Taylor | New York | 1955 | 1 |

| Sarah Connor | Los Angeles | 1959 | 1 |

+-----------------+-------------+------+----+

Edit - added GROUP BY City to outer query, as people with same birth years would return multiple values. Grouping on the outer query ensures that only one result will be returned per city, if more than one person has that minimum Birthyear. The ct column will show if more than one person exists in the city with that Birthyear

編輯 - 將GROUP BY City添加到外部查詢，因為具有相同出生年份的人將返回多個值。對外部查詢進行分組可確保每個城市只返回一個結果，如果有多個人具有該最小的Birthyear。 ct列將顯示具有該Birthyear的城市中是否存在多個人

#2

3

This is probably not the most elegant and quickest solution, but it should work. I am looking forward the see the solutions of real database gurus.

這可能不是最優雅，最快捷的解決方案，但應該可行。我期待看到真正的數據庫大師的解決方案。

select p.* from people p,

(select city, max(age) as mage from people group by city) t

where p.city = t.city and p.age = t.mage

order by p.age desc

#3

2

Something like that?

那樣的東西？

SELECT

Id, Name, City, Birthyear

FROM

TheTable

WHERE

Id IN (SELECT TOP 1 Id FROM TheTable i WHERE i.City = TheTable.City ORDER BY Birthyear)

#4

1

Not pretty but should work also with multiple people with the same dob:

不漂亮，但也應該與具有相同dob的多個人一起工作：

Test data:

測試數據：

select id, name, city, dob

into people

from

(select 1 id,'Egon Spengler' name, 'New York' city , 1957 dob

union all select 2, 'Mac Taylor','New York', 1955

union all select 3, 'Sarah Connor','Los Angeles', 1959

union all select 4, 'Jean-Luc Picard','La Barre', 2305

union all select 5, 'Ellen Ripley','Nostromo', 2092

union all select 6, 'James T. Kirk','Riverside', 2233

union all select 7, 'Henry Jones','Chicago', 1899

union all select 8, 'Blah','New York', 1955) a

Query:

查詢：

select

*

from

people p

left join people p1

ON

p.city = p1.city

and (p.dob > p1.dob and p.id <> p1.id)

or (p.dob = p1.dob and p.id > p1.id)

where

p1.id is null

order by

p.dob

#5

1

@BlaM

@BlaM

UPDATED just found that its good to use USING instead of ON. it will remove duplicate columns in result.

UPDATED剛發現使用USING代替ON很好。它將刪除結果中的重復列。

SELECT P.*, COUNT(*) AS ct

FROM people P

JOIN (SELECT City, MIN(Birthyear) AS Birthyear

FROM people

GROUP by City) P2 USING(Birthyear, City)

GROUP BY P.City

ORDER BY P.Birthyear ASC

LIMIT 10;

ORIGINAL POST

原始郵政

hi, i've tried to use your updated query but i was getting wrong results until i've added extra condition to join (also extra column into join select). transfered to your query, i'am using this:

嗨，我已經嘗試使用您更新的查詢但我得到了錯誤的結果，直到我添加額外的條件加入(也加入選擇的額外列)。轉移到您的查詢，我使用這個：

SELECT P.*, COUNT(*) AS ct

FROM people P

JOIN (SELECT City, MIN(Birthyear) AS Birthyear

FROM people

GROUP by City) P2 ON P2.Birthyear = P.Birthyear AND P2.City = P.City

GROUP BY P.City

ORDER BY P.Birthyear ASC

LIMIT 10;

in theory you should not need last GROUP BY P.City, but i've left it there for now, just in case. will probably remove it later.

從理論上講，你不應該需要最后的GROUP BY P.City，但我現在就把它留在那里，以防萬一。可能會在以后刪除它。