/******************************************/
/*回复：代码20080518001 总：00000000019 */
/*主题：excpt all */
/*作者：二等草 */
/******************************************/
set nocount on
/************例子数据 begin****************/
create table ta (rno int,name varchar(10),deptname varchar(10),jine int)
insert ta select 1, 'aa', 'rl' ,5
insert ta select 1, 'aa' , 'rl' , 5
insert ta select 1, 'aa' , 'rl' , 5
insert ta select 2, 'cc' , 'cw' , 6
insert ta select 2, 'cc' , 'cw' , 6
insert ta select 3, 'bb', 'xx' ,5
create table tb (rno int, name varchar(10),deptname varchar(10),jine int)
insert tb select 1, 'aa', 'rl', 5
insert tb select 2, 'cc', 'cw', 6
go
/************例子数据 end******************/
/************代码 begin***************/
if object_id('tempdb..#') is not null drop table #
select *,taid=0,tas=checksum(*) into # from ta order by checksum(*)
declare @i int,@s bigint
select @s = 0
update # set taid = @i ,@i = case when @s =tas then @i +1 else 1 end,@s =tas
delete a from # a where taid<=(select count(*) from tb where checksum(*) = a.tas)
select * into #1 from #
if 1=1 alter table #1 drop column taid,tas
select * from #1
drop table #,#1
go
/************代码 end*****************/
/************结果 begin***************
rno name deptname jine
----------- ---------- ---------- -----------
1 aa rl 5
1 aa rl 5
2 cc cw 6
3 bb xx 5
************结果 end*****************/
/************清除*************************/
drop table ta,tb
