MySQL - 选择按字母顺序排在第一位的名称
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30231937/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL - SELECT the name that comes first alphabetically
提问by Dylan
I have started to learn MySQL.
我已经开始学习MySQL。
Here is the table world:
这是表world：
+-------------+-----------+---------+
| name | continent | area |
+-------------+-----------+---------+
| Afghanistan | Asia | 652230 |
| Albania | Europe | 2831741 |
| Algeria | Africa | 28748 |
| ... | ... | ... |
+-------------+-----------+---------+
I need:
我需要：
List each continent and the name of the country that comes first alphabetically
按字母顺序列出每个大陆和国家的名称
The result of SELECT must be:
SELECT 的结果必须是：
+---------------+---------------------+
| continent | name |
+---------------+---------------------+
| Africa | Algeria |
| Asia | Afghanistan |
| Caribbean | Antigua and Barbuda |
| Eurasia | Armenia |
| Europe | Albania |
| North America | Belize |
| Oceania | Australia |
| South America | Argentina |
+---------------+---------------------+
回答by dnoeth
This is a simple aggegation:
这是一个简单的聚合：
SELECT continent, MIN(name) AS name
FROM world
GROUP BY continent
ORDER by continent
回答by Oleg Kuts
If it's an Exercise from SQLZoo, than IMO it should look something like this:
select continent, name from world x
where name = (select name
from world y
where x.continent = y.continent
order by name asc
limit 1)
P.S. I study SQL from there now and this post helped me. Thanks to @Parado!)
PS 我现在从那里学习 SQL，这篇文章帮助了我。感谢@Parado！）
Update: I've found this sitewith answers. Useful if stack.
更新：我发现这个网站有答案。如果堆叠很有用。
回答by Robert
Try this
尝试这个
select distinct w.continent,
(select w2.name
from world w2
where w.continent = w2.continent
order by name asc
limit 1) name
from world w
order by w.continent
回答by Dmitry Surkis
The SqlZoo solution would better look like this:
SqlZoo 解决方案最好是这样的：
SELECT continent, name FROM world x
WHERE name <= ALL
(SELECT name FROM world y WHERE y.continent=x.continent)
回答by mazen
SELECT distinct x.continent , x.name
FROM world x ,world y
WHERE x.name = (SELECT y.name FROM world y
WHERE y.continent=x.continent order by y.name asc limit 1 ) ;
回答by Sarth
SELECT continent,
name
FROM world x
WHERE name=
(SELECT name
FROM world y
WHERE x.continent=y.continent
ORDER BY name
LIMIT 1)
This is correlated/ synchronous query.
这是相关/同步查询。
回答by Chhun Panharath
what about this sql :
这个 sql 怎么样：
select distinct continent,
(select name
from world y where y.continent = x.continent limit 1 ) as name
from world x
回答by user4990905
select continent, name from world group by continent order by name
回答by Makis
Try this
尝试这个
SELECT continent, name FROM world ORDER BY name ASC;
回答by Luis Alberto
If you need list each continent alphabetically, you have use
如果您需要按字母顺序列出每个大陆，您可以使用
SELECT * from world ORDER by continent
But, If you nedd list each country your have use
但是，如果您需要列出您使用的每个国家/地区
SELECT * from world ORDER by name
