sql - MS Access - 通过连接一些列来查找重复值

Question

我有一个有 5 列的表。我需要使用前 3 列查找重复值。

这是我的数据集的示例：

这是我需要的输出：

这是我尝试使用的脚本：

select 
    count ([Tracking id] as [Count],
    [Hierarchy], 
    [Requirement ID], 
    [Tracking ID2], 
    [Status]
From 
    [table 1]
group by
    [Hierarchy], 
    [Requirement ID], 
    [Tracking ID2], 
    [Status]
having 
    count ([Tracking id]) > 1

显然这是行不通的，因为您无法将 [Tracking ID2] 和 [Status] 分组。

我需要一个可以在 MS Access 中工作的解决方法。

标签： sqlms-accessms-access-2010

Answer 1

你可以用它subquery来实现这个

select 
    [Tracking id],
    [Hierarchy], 
    [Requirement ID], 
    [Tracking ID2], 
    [Status]
from
 [table 1] 
where [Tracking id] in (
     select 
       [Tracking id]
     From 
       [table 1]
     group by
       [Tracking id]   
     having count ([Tracking id]) > 1)

或者

 where exists (
         select 
           1
         From 
           [table 1]
         group by
           [Tracking id]   
         having count ([Tracking id]) > 1)

包括 count()，这是选项之一，通过使用join

  select 
        t1.ct,
        t1.[Tracking id],
        [Hierarchy], 
        [Requirement ID], 
        [Tracking ID2], 
        [Status]
  from [table 1] t
  inner join
    (select 
        [Tracking id],
        count(1) as ct
    from [table 1]  
    group by [Tracking id]   
    having count ([Tracking id]) > 1)) as t1 on t1.[Tracking id] = t.[Tracking id]