SQL:列出来自多个连接语句的重复记录? [英] SQL:listing duplicate records from multiple join statements ?

问题描述

地狱 下面的查询将联接多个表后返回所有员工

Helllo The below query is returning all the employees after joining multiple tables

   select e.* 
    from dbo.EMP e
    join dbo.HREMP a 
      on a.ID    = e.ID
    join dbo.LOGO c
      on c.EMPID = e.id 
    join dbo.LOGOACC d
      on d.BADGENO = c.BADGENO  
     and d.ACCLVID in (2191, 2292, 2293, 2294, 2295, 2296, 2297)

总共有653条记录返回

现在执行以下查询后

Now after executing the below query

with EmployeeCTE as
(
   select e.* 
        from dbo.EMP e
        join dbo.HREMP a 
          on a.ID    = e.ID
        join dbo.LOGO c
          on c.EMPID = e.id 
        join dbo.LOGOACC d
          on d.BADGENO = c.BADGENO  
         and d.ACCLVID in (2191, 2292, 2293, 2294, 2295, 2296, 2297)
)
select k.id  from EmployeeCTE k
group  by ID
having count (k.id) >1

我发现有135条记录的计数超过1.

如何列出具有多个计数的所有记录?

How to list all the records which is having more than one count?

推荐答案

select * 
from (select e.*  
           , count(*) over (partition by e.id) as cnt
        from dbo.EMP e
        join dbo.HREMP a 
          on a.ID    = e.ID
        join dbo.LOGO c
          on c.EMPID = e.id 
        join dbo.LOGOACC d
          on d.BADGENO = c.BADGENO  
         and d.ACCLVID in (2191, 2292, 2293, 2294, 2295, 2296, 2297) 
     ) tt 
where tt.cnt > 1

这篇关于SQL:列出来自多个连接语句的重复记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！