在 SQL Server 2008 中使用 select where 查找一列上的重复项
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13920769/
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
Finding duplicates on one column using select where in SQL Server 2008
提问by vbNewbie
I am trying to select rows from a table that have duplicates in one column but also restrict the rows based on another column. It does not seem to be working correctly.
我试图从一个表中选择一列中有重复项的行，但也限制基于另一列的行。它似乎无法正常工作。
select Id,Terms from QueryData
where Track = 'Y' and Active = 'Y'
group by Id,Terms
having count(Terms) > 1
If I remove the whereit works fine but I need to restrict it to these rows only.
如果我删除where它，它工作正常，但我只需要将其限制为这些行。
ID Terms Track Active
100 paper Y Y
200 paper Y Y
100 juice Y Y
400 orange N N
1000 apple Y N
Ideally the query should return the first 2 rows.
理想情况下，查询应返回前 2 行。
回答by Aleksandr Fedorenko
SELECT Id, Terms, Track, Active
FROM QueryData
WHERE Terms IN (
SELECT Terms
FROM QueryData
WHERE Track = 'Y' and Active = 'Y'
GROUP BY Terms
HAVING COUNT(*) > 1
)
Demo on SQLFiddle
Data:
数据：
ID Terms Track Active
100 paper Y Y
200 paper Y Y
100 juice Y Y
400 orange N N
1000 apple Y N
Results:
结果：
Id Terms Track Active
100 paper Y Y
200 paper Y Y
回答by nidu
Don't exactly get what you're doing. You use count(Terms)in havinghowever Termsis in your selectclause. It means that for each records count(Terms)will be 1. Probably you have to exclude Termsfrom selectlist.
Honestly i reproduced your table and query and it doesn't work.
不要完全明白你在做什么。你在你的子句中使用count(Terms)inhaving但是。这意味着每条记录将为 1。可能您必须从列表中排除。老实说，我复制了您的表格和查询，但它不起作用。Termsselectcount(Terms)Termsselect
Probably thisis what you're looking for(?):
可能这就是你要找的（？）：
select Id, count(Terms) from QueryData
where Track = 'Y' and Active = 'Y'
group by Id
having count(Terms) > 1
回答by Michael Fredrickson
This will return all duplicated terms meeting the criteria:
这将返回符合条件的所有重复项：
select Terms
from QueryData
where Track = 'Y' and Active = 'Y'
group by Terms
having count(*) > 1
http://sqlfiddle.com/#!3/18a57/2
http://sqlfiddle.com/#!3/18a57/2
If you want all the details for these terms, you can join to this result.
如果您想要这些条款的所有详细信息，您可以加入此结果。
;with dups as (
select Terms
from QueryData
where Track = 'Y' and Active = 'Y'
group by Terms
having count(*) > 1
)
select
qd.ID, qd.Terms, qd.Track, qd.Active
from
QueryData qd join
dups d on qd.terms = d.terms
