create table #a(商品 varchar(10),产地 int)
goinsert into #a
select 'A',1
union all
select 'A',2
union all
select 'B',1
create table #b(商品 varchar(10),产地 int)
goinsert into #b
select 'A',1
union all
select 'A',3
union all
select 'B',1
union all
select 'B',2
union all
select 'C',1
要查找出在#a表中产地没有在#b表中出现的商品。
查找结果要是:
物料 产地
A 2
goinsert into #a
select 'A',1
union all
select 'A',2
union all
select 'B',1
create table #b(商品 varchar(10),产地 int)
goinsert into #b
select 'A',1
union all
select 'A',3
union all
select 'B',1
union all
select 'B',2
union all
select 'C',1
要查找出在#a表中产地没有在#b表中出现的商品。
查找结果要是:
物料 产地
A 2
where checksum(*) not in(select CHECKSUM(*) from #b )
select * from #a except #b
select * from #a
except
select * from #b/*
商品 产地
---------- -----------
A 2(1 行受影响)
insert into #tb
select 1,'数学','1001' union all
select 2,'英语','1001' union all
select 3,'语文','1001' union all
select 4,'数学','1002' union all
select 5,'语文','1002' union all
select 6,'化学','1003' union all
select 7,'政治','1003' union all
select 8,'地理','1002' union all
select 9,'数学','1003' union all
select 10,'历史','1002' with cte as(
select *, ROW_NUMBER() over(partition by studentId order by getdate()) AS rowid from #tb)
select id,className,
case when rowid>1 then '' else studentId end as studentId
from cte
select a.* from (select top 900010 * from tba)a left join (select top 900000 * from tba) b on a.id=b.id where a.id is nullselect top 10 * from tba where id not in (select top 900000 id from tba)gocreate table #a(商品 varchar(10),产地 int)
insert into #a
select 'A',1
union all
select 'A',2
union all
select 'B',1
create table #b(商品 varchar(10),产地 int)
insert into #b
select 'A',1
union all
select 'A',3
union all
select 'B',1
union all
select 'B',2
union all
select 'C',1
select * from #a
select * from #b
select * from #a where not exists (select 1 from #b where #b.产地=#a.产地 and #b.商品=#a.商品)
商品 产地
---------- -----------
A 2(1 行受影响)
SELECT * FROM #a WHERE NOT EXISTS(SELECT 1 FROM #b WHERE #a.商品 = #b.商品 AND #a.产地 = #b.产地)