SQL 带 WHERE 的 DISTINCT 子句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5610528/
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
DISTINCT clause with WHERE
提问by Mohit
How can I use the DISTINCT clause with WHERE? For example:
如何在 WHERE 中使用 DISTINCT 子句？例如：
SELECT * FROM table WHERE DISTINCT email; -- email is a column name
I want to select all columns from a table with distinct email addresses.
我想从具有不同电子邮件地址的表中选择所有列。
回答by Adam Matan
If you mean all columns whose email is unique:
如果您的意思是电子邮件唯一的所有列：
SELECT * FROM table WHERE email in
(SELECT email FROM table GROUP BY email HAVING COUNT(email)=1);
回答by Harry Joy
May be by :
可能由：
SELECT DISTINCT email,id FROM table where id='2';
回答by Mikael Eriksson
select t1.*
from YourTable as t1
inner join
(select email
from YourTable
group by email
having count(email) = 1 ) as t2
on t1.email = t2.email
回答by that1girl.cyborg
You can use the HAVINGclause.
您可以使用该HAVING条款。
SELECT *
FROM tab_name
GROUP BY email_id
HAVING COUNT(*) = 1;
回答by Alec Scott
Try:
尝试：
SELECT * FROM table GROUP BY email
SELECT * FROM table GROUP BY email
- This returns all rows with a unique email taken by the first ID appearance (if that makes sense)
- I assume this is what you were looking since I had about the same question but none of these answers worked for me.
- 这将返回第一个 ID 外观采用的唯一电子邮件的所有行（如果有道理）
- 我认为这就是你正在寻找的，因为我有同样的问题，但这些答案都不适合我。
回答by Ivan
SELECT DISTINCT dbo.Table.Email,dbo.Table.FirstName dbo.Table.LastName, dbo.Table.DateOfBirth (etc) FROM dbo.Table.Contacts WHERE Email = 'name@email';
SELECT DISTINCT dbo.Table.Email,dbo.Table.FirstName dbo.Table.LastName, dbo.Table.DateOfBirth (etc) FROM dbo.Table.Contacts WHERE Email = 'name@email';
回答by Zahid Tanveer
simple query this query select all record from table where email is unique:
简单查询 此查询从表中选择电子邮件唯一的所有记录：
select distinct email,* from table_name回答by Kamyar
You can use ROW_NUMBER(). You can specify where conditions as well. (e.g. Name LIKE'MyName%in the following query)
您可以使用 ROW_NUMBER()。您也可以指定 where 条件。（例如Name LIKE'MyName%在以下查询中）
SELECT *
FROM (SELECT ID, Name, Email,
ROW_NUMBER() OVER (PARTITION BY Email ORDER BY ID) AS RowNumber
FROM MyTable
WHERE Name LIKE 'MyName%') AS a
WHERE a.RowNumber = 1
回答by Lu Ji
One simple query will do it:
一个简单的查询就可以做到：
SELECT *
FROM table
GROUP BY email
HAVING COUNT(*) = 1;
回答by caitan correia
Wouldn't this work:
这不是行得通吗：
SELECT email FROM table1 t1
where UNIQUE(SELECT * FROM table1 t2);
