使用DISTINCT关键字的MySQL查询 [英] MySQL query with DISTINCT keyword

问题描述

我的MySQL表country_phone_codes看起来像这样

My MySQL table country_phone_codes looks something like this

id     country_code     area_code     name
------------------------------------------------------------
1   |   93           |  93         |  AFGHANISTAN
2   |   93           |  9370       |  AFGHANISTAN - MOBILE
3   |   93           |  9375       |  AFGHANISTAN - MOBILE
4   |   355          |  355        |  ALBANIA
5   |   355          |  35568      |  ALBANIA - MOBILE - AMC
6   |   213          |  213        |  ALGERIA
7   |   213          |  2131       |  ALGERIA - CAT
------------------------------------------------------------

这些只是28000多个记录中的几个记录.我正在尝试制定一个查询，它将为我提供这样的结果-

These are just few records of more than 28000 records. I am trying to formulate a query that will provide me with the result like this-

country_code    name
-----------------------------
93            |  AFGHANISTAN
355           |  ALBANIA
213           |  ALGERIA
-----------------------------

通过使用SELECT DISTINCT(country_code) FROM country_phone_codes ORDER BY country_code LIMIT 0,260，我可以获取不同的国家/地区代码.但是，如何获得相应的国家/地区名称?

By using SELECT DISTINCT(country_code) FROM country_phone_codes ORDER BY country_code LIMIT 0,260, I am able to get distinct country codes. But how do I get corresponding country name?

推荐答案

使用GROUP BY的答案很简单:

SELECT country_code,MIN(name)
FROM country_phone_codes
GROUP BY country_code;

GROUP BY不需要DISTINCT功能和ORDER BY.由于最初的问题专门针对MySQL，因此MIN()聚合函数不是必需的，如果满足以下所有条件，您可能会看到更好的性能:

The DISTINCT function and ORDER BY aren’t necessary with GROUP BY. As the original question specifically pertained to MySQL, the MIN() aggregate function isn’t necessary and you might see better performance without it if all of the following are true:

• 服务器是MySQL
• 存储引擎是InnoDB
• 示例数据的第一列是主键，条目遵循小样本建议的相同顺序，即国家名称出现在组中的所有其他名称之前.

之所以可行，是因为InnoDB存储引擎将按照主键的顺序进行扫描，并且对于未聚合的列，它将使用找到的第一个值.

This works because the InnoDB storage engine will scan in the order of the primary key and, for nonaggregated columns, it will use the first value it finds.

这篇关于使用DISTINCT关键字的MySQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！