大小写不敏感的重复SQL

Question

我有一个用户表，其中用户的用户名存在许多重复，例如：

username、Username 和 useRnAme
john、John 和 jOhn

这是一个错误，这三条记录应该只有一条。

我正在尝试编写一个 SQL 查询，按创建日期列出所有这些重复记录，所以理想情况下结果应该像这样：

username jan01
useRnAme jan02
Username jan03
john     feb01 
John     feb02
jOhn     feb03

任何建议都会非常感激。

- hdx

---

@hdx：你的问题被标记为 mysql 和 postgresql。你两个都在使用吗？ - Peter Lang

@hdx：你是否实际上是将日期存储在该格式中而不是日期列中？ - Mark Byers

@Peter Lang，实际上任何类似 SQL 的语言都可以，我可以进行移植。我正在使用 postgresql。 - hdx

@Mark Byers 这只是一个基本示例，它是日期格式。 - hdx

@hdx：最好为每个数据库编写不同的查询。试图编写适用于所有数据库的查询通常是一个坏主意。 - Mark Byers

谢谢大家，我真的超级喜欢这个网站 :P - hdx

Answer 1

暂不考虑大小写的问题，基本策略是：

 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

许多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

- Larry Lustig

---

如果是针对MYSQL的话，不需要使用UPPER函数，甚至可能会使查询变得不必要的缓慢。 - Mark Byers

是的，这是真的（对于其他各种关系型数据库管理系统也是如此）。我会修改答案以反映这一点。 - Larry Lustig

有没有办法确保每个重复组的日期按升序排列？ - hdx

你有包含 ORDER BY 子句吗？并且对于你的数据库，列是否已经适当更改了？ - Larry Lustig

好的，我找到问题了...我们需要在最后加上"UPPER(username)，create_date"。感谢您的帮助！ - hdx

我不确定我理解为什么会这样，但我很高兴你找到了解决方案。 - Larry Lustig

Answer 2

尝试类似这样的东西

SELECT UserName, CreatedDate
FROM User
WHERE LOWER(TRIM(UserName)) IN 
(
SELECT LOWER(TRIM(UserName))
FROM User
GROUP BY LOWER(TRIM(UserName))
HAVING count(*) > 1
)

- Christoph

---

哎呀，我看到Larry先发了同样的内容。 - Christoph

Answer 3

这就是我想出来的。这个代码是用postgres数据库编写的，但是应该可以在其他SQL引擎中正常工作。

select * from user u join user u2
on upper(u.email)=upper(u2.email) where u.id != u2.id
order by u.email;

因此，查询假定电子邮件是重复的，但ID不是，因此它正在寻找具有重复电子邮件（不区分大小写）但具有唯一ID的记录。

- Badmous

Answer 4

在MySQL中，使用二进制排序来进行区分大小写的比较。因此，您可以在表格本身上执行连接操作，查找区分大小写的比较与不区分大小写的比较结果不同的行：

select *
from YourTable t1
inner join YourTable t2 
on t1.name <> t2.name collate latin1_bin
and t1.name = t2.name

- Andomar

Answer 5

在你的SELECT语句中使用ToLower()或等效函数，并按该列排序。

- 3Dave

---

这将包括不受多次输入问题困扰的用户名。 - Larry Lustig

Answer 6

SELECT UserName, CreatedDate
FROM YourTable 
WHERE UserName COLLATE UTF8_BIN != LOWER(UserName COLLATE UTF8_BIN)
GROUP BY UserName, CreatedDate
HAVING COUNT(*) > 1

- ShadowTK

---

从审核队列中：我可以请求您在源代码周围添加一些上下文吗？仅有代码的答案很难理解。如果您能在帖子中添加更多信息，这将有助于提问者和未来的读者。 - RBT