在MySQL中仅选择唯一的行/记录 [英] Select only unique row/record in mysql

问题描述

我想看看如何仅获得具有唯一城市的唯一行/记录，而不在乎是否是首都，例如:

I would like to see how I can get only unique row/records that have a unique city and not care if it's capital so for example:

Akron
akron
aKRON

只会返回一条记录.

我累了像这样的东西，但是没用

I tired something like this but it doesn't work

"SELECT DISTINCT(city) AS city,state_prefix,lattitude,longitude  FROM zip_code WHERE city LIKE '$queryString%' LIMIT 10"

谢谢...

推荐答案

您可以使用以下仅用于mysql的技巧:

You can use this mysql-only trick:

SELECT city, state_prefix, lattitude,longitude
FROM zip_code WHERE city LIKE '$queryString%'
GROUP BY city, state_prefix -- Here's the trick
LIMIT 10

这将为city和state_prefix的每个唯一值返回遇到的第一行.

This will return the first row encountered for each unique value of city and state_prefix.

其他数据库会抱怨您的分组依据中未列出未聚合的列或某些此类消息.

Other databases will complain that you have non=-aggregated columns not listed in the group by or some such message.

以前，我声称不对分组的列使用upper()函数，它将返回所有大小写的变化，但这是不正确的-感谢SalmanA指出了这一点.我使用SQLFiddle进行了验证，您无需使用upper().

Previously I claimed that not using the upper() function on the grouped-by columns it would return all case variations, but that was incorrect - thanks to SalmanA for pointing that out. I verified using SQLFiddle and you don't need to use upper().

这篇关于在MySQL中仅选择唯一的行/记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！