求Sql语句，困惑了一天时间
create table a(id int identity,aid varchar(5),name varchar(6))
insert into a select 1,'a'
insert into a select 1,'a'
insert into a select 1,'a'
insert into a select 1,'b'
insert into a select 1,'b'
insert into a select 1,'c'
insert into a select 2,'a'
insert into a select 2,'a'

create table b(id int identity,aid varchar(5),name varchar(6))
insert into b select 1,'a'
insert into b select 1,'a'
insert into b select 1,'b'
insert into b select 1,'b'
insert into b select 1,'b'
insert into b select 1,'d'



a表和b表通过aid 相关联
在a.aid=b.aid的情况下，
比较a表和b表:
a表比b表多一个'a',就在a表中删除一个'a'
a表比b表少一个'b',就在a表中插入一个'b'
a表中没有 'd' 就在a表中插入'd'
b表中没有 'c' 就在a表中删除'c'


使a表最终结构变为：

select * from a

id aid name
----------- ----- ------
1 1 a
2 1 a
4 1 b
5 1 b
7 2 a
8 2 a
9 1 b
10 1 d  


------解决方案--------------------
create table a(id int identity,aid varchar(5),name varchar(6)) 
insert into a select 1, 'a ' 
insert into a select 1, 'a ' 
insert into a select 1, 'a ' 
insert into a select 1, 'b ' 
insert into a select 1, 'b ' 
insert into a select 1, 'c ' 
insert into a select 2, 'a ' 
insert into a select 2, 'a ' 

create table b(id int identity,aid varchar(5),name varchar(6)) 
insert into b select 1, 'a ' 
insert into b select 1, 'a ' 
insert into b select 1, 'b ' 
insert into b select 1, 'b ' 
insert into b select 1, 'b ' 
insert into b select 1, 'd ' 
go
select m.id,m.aid,m.name from
(
select px=(select count(1) from a where aid=t.aid and name = t.name and id<t.id)+1 , * from a t
) m 
where cast(px as varchar) + ',' + cast(aid as varchar) + ',' + cast(name as varchar) in
(
select cast(px as varchar) + ',' + cast(aid as varchar) + ',' + cast(name as varchar) from
(
select px=(select count(1) from b where aid=t.aid and name = t.name and id<t.id)+1 , * from b t
) n
)
union all
select m.id,m.aid,m.name from
(
select px=(select count(1) from b where aid=t.aid and name = t.name and id<t.id)+1 , * from b t
) m 
where cast(px as varchar) + ',' + cast(aid as varchar) + ',' + cast(name as varchar) not in
(
select cast(px as varchar) + ',' + cast(aid as varchar) + ',' + cast(name as varchar) from
(
select px=(select count(1) from a where aid=t.aid and name = t.name and id<t.id)+1 , * from a t
) n
)

drop table a,b

/*
id aid name
----------- ----- ------ 
1 1 a 
2 1 a 
4 1 b 
5 1 b 
5 1 b 
6 1 d 

（所影响的行数为 6 行）
*/
------解决方案--------------------
create table a(id int identity,aid varchar(5),name varchar(6)) 
insert into a select 1, 'a ' 
insert into a select 1, 'a ' 
insert into a select 1, 'a ' 
insert into a select 1, 'b ' 
insert into a select 1, 'b ' 
insert into a select 1, 'c ' 
insert into a select 2, 'a ' 
insert into a select 2, 'a ' 

create table b(id int identity,aid varchar(5),name varchar(6)) 
insert into b select 1, 'a ' 
insert into b select 1, 'a ' 
insert into b select 1, 'b ' 
insert into b select 1, 'b ' 
insert into