从 SQL 表中消除重复的行。

huangapple
go评论106阅读模式
英文:

Eliminate the duplicate rows from the table in SQL

问题

我想根据电子邮件地址从表格中消除重复的行，并检索所有没有重复的行。

我尝试使用DISTINCT，但我没有得到期望的结果。

  1. SELECT 
  2.   DISTINCT Email 
  3. FROM 
  4.   Users

示例表格：

Id Email Username
1 sam@gmail.com sam1122
2 john@gmail.com john1122
3 sam@gmail.com sam2233
4 lily@gmail.com lily@as

我想要检索的内容：

Id Email Username
1 john@gmail.com john1122
2 lily@gmail.com lily@as
英文:

I want to eliminate the duplicate rows based on email from the table and retrieve all the rows without duplicates.

I have tried using distinct but I'm not getting desired results.

  1. SELECT 
  2.   DISTINCT Email 
  3. FROM 
  4.   Users

Example Table:

Id Email Username
1 sam@gmail.com sam1122
2 john@gmail.com john1122
3 sam@gmail.com sam2233
4 lily@gmail.com lily@as

What I want to retrieve:

Id Email Username
1 john@gmail.com john1122
2 lily@gmail.com lily@as

答案1

得分: 1

我们可以尝试在这里使用存在性逻辑：

  1. SELECT Id, Email, Username
  2. FROM Users u1
  3. WHERE NOT EXISTS (
  4.     SELECT 1
  5.     FROM Users u2
  6.     WHERE u2.Email = u1.Email AND
  7.           u2.Id <> u1.Id
  8. );
英文:

We can try using exists logic here:

<!-- language: sql -->

  1. SELECT Id, Email, Username
  2. FROM Users u1
  3. WHERE NOT EXISTS (
  4.     SELECT 1
  5.     FROM Users u2
  6.     WHERE u2.Email = u1.Email AND
  7.           u2.Id &lt;&gt; u1.Id
  8. );

答案2

得分: 0

  1. 从用户中选择 Id、Email 和 Username
  2. 其中 Email 在 (
  3.     从用户中选择 Email
  4.     分组并且计数为 1
  5.     )
英文: 
  1. SELECT Id, Email, Username
  2. FROM Users
  3. WHERE Email IN (
  4.     SELECT Email
  5.     FROM Users
  6.     GROUP BY Email
  7.     HAVING COUNT(*) = 1
  8. )

答案3

得分: 0

你可以使用 left join 来实现：

  1. select u.*
  2. from Users u
  3. left join (
  4.   select email, max(id) as Id
  5.   from Users
  6.   group by email
  7.   having count(1) > 1
  8. ) as s on s.email = u.email
  9. where s.email is null;

在此查看示例

英文:

You can do it using left join :

  1. select u.*
  2. from Users u
  3. left join (
  4.   select email, max(id) as Id
  5.   from Users
  6.   group by email
  7.   having count(1) &gt; 1
  8. ) as s on s.email = u.email
  9. where s.email is null;

Demo here

答案4

得分: 0

以下是翻译的内容：

如果您正在使用MySQL 8，另一种选项是 -

  1. SELECT Id, Email, Username
  2. FROM (
  3.     SELECT *, COUNT(*) OVER (PARTITION BY Email) AS cnt
  4.     FROM Users
  5. ) t
  6. WHERE t.cnt = 1;
英文:

Yet another option, if you are using MySQL 8 -

  1. SELECT Id, Email, Username
  2. FROM (
  3.     SELECT *, COUNT(*) OVER (PARTITION BY Email) AS cnt
  4.     FROM Users
  5. ) t
  6. WHERE t.cnt = 1;

答案5

得分: -1

  1. SELECT id,
  2.    Email,
  3.    Username,
  4.    count(*) AS duplicate_email_count
  5. FROM Users
  6. GROUP BY Email
  7. HAVING duplicate_email_count=1
英文: 
  1. SELECT id,
  2.    Email,
  3.    Username,
  4.    count(*) AS duplicate_email_count
  5. FROM Users
  6. GROUP BY Email
  7. HAVING duplicate_email_count=1

huangapple
  • 本文由 发表于 2023年2月8日 14:53:51
  • 转载请务必保留本文链接：https://go.coder-hub.com/75382272.html
  • mysql
  • sql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定