在SQL Server 2008中使用select where在一列上查找重复项

问题描述：

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

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在一列上查找重复项

相关推荐