比如有两个表table A
NO LINE money
no1 1 100
no1 2 150
no2 1 15
.....table B
NO line
no1 2
no2 1怎样将A对比B,去掉A表中重复B表中的数据
就是去掉A表在B表中相同的数据.
NO LINE money
no1 1 100
no1 2 150
no2 1 15
.....table B
NO line
no1 2
no2 1怎样将A对比B,去掉A表中重复B表中的数据
就是去掉A表在B表中相同的数据.
调试欢乐多
FROM tba AS a
WHERE NOT EXISTS(SELECT *
FROM tbb
WHERE no=a.no
AND line=a.line)
declare @a table
(
[NO] char(3),
line int,
[money] int
)declare @b table
(
[NO] char(3),
line int
)insert @a select 'no1',1,100
union all select 'no1',2,150
union all select 'no2',1,15insert @b select 'no1',2
union all select 'no2',1select
a.*
from @a a
left join @b b
on b.[NO] = a.[NO]
and b.line = a.line
where b.[NO] is null/**
no1 1 100
**/
declare @a table
(
[NO] char(3),
line int,
[money] int
)declare @b table
(
[NO] char(3),
line int
)insert @a select 'no1',1,100
union all select 'no1',2,150
union all select 'no2',1,15insert @b select 'no1',2
union all select 'no2',1select
a.*
from @a a
left join @b b
on b.[NO] = a.[NO]
and b.line = a.line
where b.[NO] is null/**
no1 1 100
**/
where not exists(select * from @tb where no=a.no AND line=a.line)select a.* from @ta a left join @tb b on b.no=a.no AND b.line=a.line
where b.NO is null and b.line is null
(
[NO] char(3),
line int,
[money] int
)
declare @b table
(
[NO] char(3),
line int
)
insert @a select 'no1',1,100
union all select 'no1',2,150
union all select 'no2',1,15insert @b select 'no1',2
union all select 'no2',1select m.* from @a m where not exists(select 1 from @b n where no = m.no and line = m.line)/*
NO line money
---- ----------- -----------
no1 1 100(所影响的行数为 1 行)
*/
select * from a where not exists(select * from B where no=a.no and line=a.line)
declare @a table(no varchar(3),line int,m money)
insert into @a select 'no1',1,100
union all select 'no1',2,150
union all select 'no2',1,15
declare @b table (no varchar(3),line int)
insert into @b select 'no1',2
union all select 'no2',1-------------
delete a from @a a inner join @b b on a.line=b.line and a.no=b.no
select * from @A
no1 1 100.00