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 行)
*/
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 行)
*/
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 行)
*/
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',11select top 12 id=identity(tinyint,1,1) into #Temp from syscolumnsselect 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
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)