你好
以下查询在连接多个表后返回所有员工
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 条记录
现在执行以下查询后
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 条记录的 count 超过 1 。
如何列出所有具有多个计数的记录？
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
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
