在SQL Server 2008中使用select where在一列上查找重复项 [英] Finding duplicates on one column using select where in SQL Server 2008

问题描述

我正在尝试从表中选择在一列中具有重复项的行，但还要限制基于另一列的行.它似乎无法正常工作.

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

如果我删除了where，它可以正常工作，但是我只需要将它们限制为这些行即可.

If I remove the where it works fine but I need to restrict it to these rows only.

ID      Terms     Track    Active
100     paper     Y        Y
200     paper     Y        Y
100     juice     Y        Y
400     orange    N        N
1000    apple     Y        N

理想情况下，查询应返回前2行.

Ideally the query should return the first 2 rows.

推荐答案

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
                )

在 SQLFiddle

数据:

ID      Terms     Track    Active
100     paper     Y        Y
200     paper     Y        Y
100     juice     Y        Y
400     orange    N        N
1000    apple     Y        N

结果:

Id      Terms     Track    Active
100     paper     Y        Y
200     paper     Y        Y

这篇关于在SQL Server 2008中使用select where在一列上查找重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！