求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 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
)
union all
select a.* from a where aid not in (select aid from b)
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
7 2 a
8 2 a
（所影响的行数为 8 行）
*/
