如何获取实际结果中没有的记录 [英] how to get records which are not available in actual result

问题描述

大家好，

我想从表中获取表中实际结果中没有的记录。场景如下。



我的实际结果



查询哪个我写了： -

 选择（cont.FirstName + ' ' + ISNULL（cont.LastName，' '）） as  withoutduplicationName 
 来自联系人cont  left   join 城市ct  on  cont.Location = ct.CityId  join 状态st 
  on  cont.WorkState = st。 StateId  join  Recruiters rec  on  cont.Recruiter = rec.RecruiterId 加入团队t   
 rec.Team = t.TeamId 其中 cont.IsDeleted = '  false' < span class =code-keyword>和 rec.IsDeleted = '  false' 

o / p： - 显示534（计数）记录。



消除重复。



查询如下

 选择  distinct （cont.FirstName + ' ' + ISNULL（cont.LastName，' '））  as  withoutduplicationName 
 来自联系人cont  left   join 城市ct   cont.Location = ct.CityId  join 状态st 
   cont.WorkState = st.StateId  join  Recruiters rec  on  cont.Recruiter = rec.RecruiterId  join 团队t  on  
 rec.Team = t.TeamId 其中​​ cont.IsDeleted = '  false' 和 rec.IsDeleted = '  false' 

o / P： - 显示495条记录。



现在我想获得实际结果中没有的记录。



ie 534-495 = 39条记录？



所以如何获得最新的39条记录作为o / p？

请帮我解决这个问题，我在sql中使用除了条件但它没有给出正确的o / p。

解决试试这个

 选择 
（续。名字+ < span class =code-string>' ' + ISNULL（cont.LastName，' '）） as  duplicationName，
 count（ 1 ）  NoOfRecords 
 来自联系人cont 
  left   join 城市ct  on  cont.Location = ct .CityId 
  join 状态st  on  cont.WorkState = st.StateId 
 < span class =code-keyword> join  Recruiters rec  on  cont.Recruiter = rec.RecruiterId 
 加入团队t   rec.Team = t。 TeamId 
 其中 cont.IsDeleted = '  false ' 和 rec.IsDeleted = '  false ' 
  group   by （cont.FirstName + ' ' + ISNULL（cont.LastName，' '））
  计数（ 1 ）> 1 

hi all,
i want to get the records from the table which are not available in actual result of a table. the scenario is as follows.

My Actual Result

query which i have written:-

select (cont.FirstName+' '+ISNULL(cont.LastName,'')) as withoutduplicationName
from Contacts cont left join Cities ct on cont.Location=ct.CityId join States st
on cont.WorkState=st.StateId join Recruiters rec on cont.Recruiter=rec.RecruiterId join Team t on
rec.Team=t.TeamId where cont.IsDeleted='false' and rec.IsDeleted='false'

o/p:- it is displaying 534 (count) records.

Eliminating duplicates.

query is as follows

select distinct (cont.FirstName+' '+ISNULL(cont.LastName,'')) as withoutduplicationName
from Contacts cont left join Cities ct on cont.Location=ct.CityId join States st
on cont.WorkState=st.StateId join Recruiters rec on cont.Recruiter=rec.RecruiterId join Team t on
rec.Team=t.TeamId where cont.IsDeleted='false' and rec.IsDeleted='false'

o/P:- it is displaying 495 records.

Now i want to get the records which are not available in my actual result.

i.e 534-495=39 records?

so how to get thest 39 records as an o/p?
please help me to solve this, i have used except condition in sql but it is not giving proper o/p.

解决方案

try this

select  
   (cont.FirstName+' '+ISNULL(cont.LastName,'')) as duplicationName,
    count(1) as NoOfRecords
from Contacts cont 
left join Cities ct on cont.Location=ct.CityId 
join States st on cont.WorkState=st.StateId 
join Recruiters rec on cont.Recruiter=rec.RecruiterId 
join Team t on rec.Team=t.TeamId 
where cont.IsDeleted='false' and rec.IsDeleted='false'
group by (cont.FirstName+' '+ISNULL(cont.LastName,''))
having count(1)>1

这篇关于如何获取实际结果中没有的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！