a
rno name deptname jine
1 aa rl 5
1 aa rl 5
1 aa rl 5
2 cc cw 6
2 cc cw 6
3 bb xx 5
b
rno name deptname jine
1 aa rl 5
2 cc cw 6
结果为:把b 表中没有的结果显示出来
rno name deptname jine
1 aa rl 5
1 aa rl 5
2 cc cw 6
3 bb xx 5
rno name deptname jine
1 aa rl 5
1 aa rl 5
1 aa rl 5
2 cc cw 6
2 cc cw 6
3 bb xx 5
b
rno name deptname jine
1 aa rl 5
2 cc cw 6
结果为:把b 表中没有的结果显示出来
rno name deptname jine
1 aa rl 5
1 aa rl 5
2 cc cw 6
3 bb xx 5
2005用row_number实现
a表出现重复值
/*回复:代码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
/*回复:代码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***************/
select *,taid=0,tas=checksum(*) into # from ta order by checksum(*)
exec(N'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')
exec(N'delete a from # a where taid<=(select count(*) from tb where checksum(*) = a.tas)')
if 1=1 alter table # drop column taid,tas
select * from #
drop table #
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