英文:
Eliminate the duplicate rows from the table in SQL
问题
我想根据电子邮件地址从表格中消除重复的行，并检索所有没有重复的行。
我尝试使用DISTINCT，但我没有得到期望的结果。
SELECT
DISTINCT Email
FROM
Users
示例表格：
| Id | Username | |
|---|---|---|
| 1 | sam@gmail.com | sam1122 |
| 2 | john@gmail.com | john1122 |
| 3 | sam@gmail.com | sam2233 |
| 4 | lily@gmail.com | lily@as |
我想要检索的内容：
| Id | 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.
SELECT
DISTINCT Email
FROM
Users
Example Table:
| Id | 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 | Username | |
|---|---|---|
| 1 | john@gmail.com | john1122 |
| 2 | lily@gmail.com | lily@as |
答案1
得分: 1
我们可以尝试在这里使用存在性逻辑：
SELECT Id, Email, Username
FROM Users u1
WHERE NOT EXISTS (
SELECT 1
FROM Users u2
WHERE u2.Email = u1.Email AND
u2.Id <> u1.Id
);
英文:
We can try using exists logic here:
<!-- language: sql -->
SELECT Id, Email, Username
FROM Users u1
WHERE NOT EXISTS (
SELECT 1
FROM Users u2
WHERE u2.Email = u1.Email AND
u2.Id <> u1.Id
);
答案2
得分: 0
从用户中选择 Id、Email 和 Username
其中 Email 在 (
从用户中选择 Email
分组并且计数为 1
)
英文:
SELECT Id, Email, Username
FROM Users
WHERE Email IN (
SELECT Email
FROM Users
GROUP BY Email
HAVING COUNT(*) = 1
)
答案3
得分: 0
你可以使用 left join 来实现：
select u.*
from Users u
left join (
select email, max(id) as Id
from Users
group by email
having count(1) > 1
) as s on s.email = u.email
where s.email is null;
英文:
You can do it using left join :
select u.*
from Users u
left join (
select email, max(id) as Id
from Users
group by email
having count(1) > 1
) as s on s.email = u.email
where s.email is null;
答案4
得分: 0
以下是翻译的内容：
如果您正在使用MySQL 8，另一种选项是 -
SELECT Id, Email, Username
FROM (
SELECT *, COUNT(*) OVER (PARTITION BY Email) AS cnt
FROM Users
) t
WHERE t.cnt = 1;
英文:
Yet another option, if you are using MySQL 8 -
SELECT Id, Email, Username
FROM (
SELECT *, COUNT(*) OVER (PARTITION BY Email) AS cnt
FROM Users
) t
WHERE t.cnt = 1;
答案5
得分: -1
SELECT id,
Email,
Username,
count(*) AS duplicate_email_count
FROM Users
GROUP BY Email
HAVING duplicate_email_count=1
英文:
SELECT id,
Email,
Username,
count(*) AS duplicate_email_count
FROM Users
GROUP BY Email
HAVING duplicate_email_count=1
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论