select username,sum(num) as num from ( select username,1 as num from a union all select username,-1 as num from b ) as t group by username having sum(num)<>0
select username from a where a.username +a.某一能唯一表示某一记录的字段 not in (select username from b)
多谢Haiwer(海阔天空) ,你素叔叔还是阿姨
declare @a table(id int, username varchar(20)) insert @a select 1 ,'13000000000' union all select 2, '13000000000' union all select 3, '13000000000' union all select 4, '13000000001' union all select 5 ,'13000000001' declare @b table(id int, username varchar(20)) insert @b select 1 ,'13000000000' union all select 2, '13000000000' union all select 3, '13000000001' union all select 4, '13000000001' select c.username,c.aa from (select username,count(1) as aa from @a gg group by username) c Inner Join (select username,count(1) as bb from @b ff group by username) d On c.username=d.username where c.aa>d.bb union all select username,0 from @a where username not in(select username from @b)
from (
select username,1 as num
from a
union all
select username,-1 as num
from b
) as t
group by username
having sum(num)<>0
from a
where a.username +a.某一能唯一表示某一记录的字段 not in (select username from b)
insert @a
select 1 ,'13000000000'
union all
select 2, '13000000000'
union all
select 3, '13000000000'
union all
select 4, '13000000001'
union all
select 5 ,'13000000001' declare @b table(id int, username varchar(20))
insert @b
select 1 ,'13000000000'
union all
select 2, '13000000000'
union all
select 3, '13000000001'
union all
select 4, '13000000001'
select c.username,c.aa from
(select username,count(1) as aa from @a gg group by username) c
Inner Join
(select username,count(1) as bb from @b ff group by username) d On c.username=d.username where c.aa>d.bb
union all
select username,0 from @a where username not in(select username from @b)