[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