MySQL根据一行的唯一性选择DISTINCT多列?

时间:2021-11-11 12:22:02

I'm trying to understand what this query does exactly:

我试图理解这个查询的确切作用:

SELECT DISTINCT `state`, `state_name` FROM `geo` ORDER BY `state_name` ASC

All I'm trying to do is select 2 columns (state and state_name), I want only unique rows that do not have duplicate values for the state field. I don't care if there are duplicate values in the state_name field.

我要做的就是选择2列(state和state_name),我只想要唯一的行,这些行没有州字段的重复值。我不在乎state_name字段中是否有重复的值。

Is my query checking both columns for uniqueness or just state?

我的查询是检查两列的唯一性还是只是状态?

2 个解决方案

#1


18  

DISTINCT will return only distinct rows, so:

DISTINCT将仅返回不同的行,因此:

Is my query checking both columns for uniqueness or just state?

我的查询是检查两列的唯一性还是只是状态?

Both columns

You could also switch to GROUP BY instead.

您也可以切换到GROUP BY。

SELECT `state`, `state_name` FROM `geo` group by 'state', 'state_name' ORDER BY `state_name` ASC 

#2


1  

It's checking for unique combinations of state and state_name. Distinct operates on all columns included in your select list.

它正在检查state和state_name的唯一组合。 Distinct对选择列表中包含的所有列进行操作。

To only include unique state values, just select distinct state

要仅包含唯一的状态值,只需选择不同的状态

#1


18  

DISTINCT will return only distinct rows, so:

DISTINCT将仅返回不同的行,因此:

Is my query checking both columns for uniqueness or just state?

我的查询是检查两列的唯一性还是只是状态?

Both columns

You could also switch to GROUP BY instead.

您也可以切换到GROUP BY。

SELECT `state`, `state_name` FROM `geo` group by 'state', 'state_name' ORDER BY `state_name` ASC 

#2


1  

It's checking for unique combinations of state and state_name. Distinct operates on all columns included in your select list.

它正在检查state和state_name的唯一组合。 Distinct对选择列表中包含的所有列进行操作。

To only include unique state values, just select distinct state

要仅包含唯一的状态值,只需选择不同的状态