[SQL SERVER][TSQL]找出資料表中不存在的資料

[SQL SERVER][TSQL]找出資料表中不存在的資料

網友問題,自己做個紀錄。

 

需求:依照資料比對條件,找出資料表中不存在的資料(意味筆數=0)

 

原始資料

declare @mytest table
(
id int identity(1,1) primary key,
name varchar(10)
)
insert into @mytest 
values('a'),('b'),('c'),('d'),('e'),('f'),('g'),('1'),('3'),('R')

 

 

 

資料比對條件:a,b,k

 

查詢陳述式(資料 k 不屬於該資料表)

--SQL2000
select * 
from 
(
select t1.name,count(t2.name) as 'count' 
from (select 'a' as name
union 
select 'b' as name
union 
select 'K' as name) t1 left join @mytest t2
on t1.name=t2.name
and t2.name in ('a','b','K')
group by t1.name 
) result
where [count] =0 --k不存在資料  

 



--SQL2005以後
;with mycte as
(
select t1.name,isnull(count(t2.name),0) as 'count' 
from (select 'a' as name
union 
select 'b' as name
union 
select 'K' as name) t1 left join @mytest t2
on t1.name=t2.name
and t2.name in ('a','b','K')
group by t1.name 
)
-- 列出不存在的資料=>K
select * from mycte where [count]=0