postgresql 不区分大小写的重复 SQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2694106/
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
Case insensitive duplicates SQL
提问by hdx
So I have a users table where the user.username has many duplicates like:
所以我有一个用户表，其中 user.username 有很多重复项，例如：
usernameand Usernameand useRnAmejohnand Johnand jOhn
username和Username和useRnAmejohn和John和jOhn
That was a bug and these three records should have been only one.
那是一个错误，这三个记录应该只有一个。
I'm trying to come up with a SQL query that lists all of these cases ordered by their creation date, so ideally the result should be something like this:
我正在尝试提出一个 SQL 查询，列出所有这些按创建日期排序的案例，因此理想情况下，结果应该是这样的：
username jan01
useRnAme jan02
Username jan03
john feb01
John feb02
jOhn feb03
Any suggestions will be much appreciated
任何建议将不胜感激
回答by Larry Lustig
Leaving aside the issue of case sensitivity for a moment, the basic strategy is:
暂且不谈区分大小写的问题，基本策略是：
SELECT username, create_date FROM your_table
WHERE username IN
(SELECT username FROM your_table GROUP BY username HAVING COUNT(*) > 1)
ORDER BY username, create_date
Many RDBMSes (including MySQL assuming that you are using CHAR or VARCHAR for the username column), perform case-insensitive searching by default. For those databases, the above solution will work. To solve the case sensitivity issue for other products , wrap all except the first occurrence of username in the uppercase conversion function specific to your RDBMS:
许多 RDBMS（包括 MySQL，假设您对用户名列使用 CHAR 或 VARCHAR），默认情况下执行不区分大小写的搜索。对于这些数据库，上述解决方案将起作用。要解决其他产品的区分大小写问题，请将除第一次出现的用户名之外的所有内容都包装在特定于您的 RDBMS 的大写转换函数中：
SELECT username, create_date FROM your_table
WHERE UPPER(username) IN
(SELECT UPPER(username) FROM your_table GROUP BY UPPER(username) HAVING COUNT(*) > 1)
ORDER BY username, create_date
回答by Christoph
Try something like these
尝试这样的事情
SELECT UserName, CreatedDate
FROM User
WHERE LOWER(TRIM(UserName)) IN
(
SELECT LOWER(TRIM(UserName))
FROM User
GROUP BY LOWER(TRIM(UserName))
HAVING count(*) > 1
)
回答by ShadowTK
SELECT UserName, CreatedDate
FROM YourTable
WHERE UserName COLLATE UTF8_BIN != LOWER(UserName COLLATE UTF8_BIN)
GROUP BY UserName, CreatedDate
HAVING COUNT(*) > 1
回答by 3Dave
Use ToLower() or equivalent function in your SELECT, and order by that column.
在 SELECT 中使用 ToLower() 或等效函数，并按该列排序。
回答by Andomar
In MySQL, a case-sensitive compare is done using a binary collation. So you could join the table on itself, looking for rows where the case sensitive compare is different from the case insensitive compare:
在 MySQL 中，区分大小写的比较是使用二进制排序规则完成的。因此，您可以单独加入表，查找区分大小写的比较与不区分大小写的比较不同的行：
select *
from YourTable t1
inner join YourTable t2
on t1.name <> t2.name collate latin1_bin
and t1.name = t2.name
