求Sql语句，困惑了一天时间

[ojuju10]

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

[dawugui]

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 行）
*/

[dawugui]

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 行）
*/

[dawugui]

按楼主的要求,如果A中AID有不在B中的,直接提取出来后和第一个语句union all.

[ojuju10]

谢谢乌龟兄，用union all 速度会不会比较慢！

[Limpire]

--原始数据：#T
create table #T(yd varchar(7),rs int)
insert #T
select '2006-1',5 union all
select '2006-2',8 union all
select '2006-5',10 union all
select '2006-8',3 union all
select '2006-12',10 union all
select '2007-2',11

select top 12 id=identity(tinyint,1,1) into #Temp from syscolumns

select yd=a.year+'-'+ltrim(b.id),rs=isnull(c.rs,0) from
(select year=left(yd,4) from #T group by left(yd,4)) a
cross join #Temp b
left join #T c
on a.year+'-'+ltrim(b.id)=c.yd
--到数据最大月份为止
where cast(a.year+'-'+ltrim(b.id)+'-01' as datetime)<=(select max(cast(yd+'-01' as datetime)) from #T)

select yd=a.year+'-'+ltrim(b.id),rs=isnull(c.rs,0) from
(select year=left(yd,4) from #T group by left(yd,4)) a
cross join #Temp b
left join #T c
on a.year+'-'+ltrim(b.id)=c.yd
--到当前月为止
where cast(a.year+'-'+ltrim(b.id)+'-01' as datetime)<=convert(varchar(8),getdate(),120)+'01'

--删除对象
drop table #T,#Temp

[Limpire]

抱歉！回错贴了，请楼主忽略。

[ojuju10]

能不能不用select ，只用insert 或者 delete 操作

[ojuju10]

顶

[ojuju10]

ding

[ojuju10]

问题已经解决，谢谢老乌龟兄

[ojuju10]

问题已经解决，谢谢老乌龟兄

[ojuju10]

问题已经解决，谢谢老乌龟兄！

[ojuju10]

问题已经解决，谢谢老乌龟兄！

[wuxinyuzhu]

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 '



select px=(select count(1) from a where aid = t.aid and name= t.name and id<t.id)+1,* into #tb1 from a t
select px=(select count (1) from b where aid = t.aid and name= t.name and id<t.id)+1,* into #tb2 from b t

select * from #tb1
select * from #tb2
delete a where id in
(select id from #tb1 a where not exists(select * from #tb2 b where b.px=a.px and b.aid=a.aid and b.name=a.name)
and a.aid=1)
insert into a
select aid,name from #tb2 a where not exists(select * from #tb1 b where b.px=a.px and b.aid=a.aid and b.name=a.name)