sql中逐一对比两个表的字段的值
表A
30.0000
表B
30
30
当:表A的tb1字段=表B的tb1字段时,从表A把30取出放入新表,从表B只取一个30放入新表。同理如果表A有三个30,表B有四个30,就从表A取出三个30放入新表,
表B也取三个30放入新表。。
请各位高手解决????谢谢
表A
30.0000
表B
30
30
当:表A的tb1字段=表B的tb1字段时,从表A把30取出放入新表,从表B只取一个30放入新表。同理如果表A有三个30,表B有四个30,就从表A取出三个30放入新表,
表B也取三个30放入新表。。
请各位高手解决????谢谢
if OBJECT_ID('A') is not null
drop table A;
go
create table A (num int);
go
insert into A select 30;
go
insert into A select 20 union all select 20;
go
insert into A select 10;
goif OBJECT_ID('B') is not null
drop table B;
go
create table B (num int);
go
insert into B select 30 union select 30;
go
insert into B select 20 union all select 20 union all select 20;
go;with t as(
select distinct num,
(select COUNT(*) from A where num=t.num) cnt_a,
(select COUNT(*) from B where num=t.num) cnt_b
from A t
)
select num,number
from t,master.dbo.spt_values
where type='P' and number between 1
and (case when cnt_a>cnt_b then cnt_b*2 else cnt_a*2 end);
FROM A
INNER JOIN B ON A.COL1=B.COL1
20 1
20 2
20 3
20 4
30 1
30 2
结果是对的,但还有个小小的问题,我要将产生的数据会到两个新表中,比如说目前的结果是4个20,我要把两个20分到新表A1,另两个20分到新表A2,下面的30会到新表A1,别一个30分到新表A2. 也就是说将相同数字的值对半分,装入新表A1和A2中。将再将原表的数据删除!!只要是移到A1、A2的数据在原表中删除!!
if OBJECT_ID('A') is not null
drop table A;
go
create table A (num int);
go
insert into A select 30;
go
insert into A select 20 union all select 20;
go
insert into A select 10;
goif OBJECT_ID('B') is not null
drop table B;
go
create table B (num int);
go
insert into B select 30 union select 30;
go
insert into B select 20 union all select 20 union all select 20;
go-- 加入新表 A1
select ta.num,ta.id into A1
from (select num,ROW_NUMBER() over (partition by num order by newid()) id from A) ta
join (select num,ROW_NUMBER() over (partition by num order by newid()) id from B) tb
on ta.num=tb.num and ta.id=tb.id;select * from A1;-- 加入新表 A2
select tb.num,tb.id into B1
from (select num,ROW_NUMBER() over (partition by num order by newid()) id from A) ta
join (select num,ROW_NUMBER() over (partition by num order by newid()) id from B) tb
on ta.num=tb.num and ta.id=tb.id;select * from B1;-- 从 A 表删除已经提取的
;with ta as(
select num,ROW_NUMBER() over (partition by num order by newid()) id from A
)
delete ta from ta,A1 where ta.num=A1.num and ta.id=A1.id;select * from A;-- 从 B 表删除已经提取的
;with tb as(
select num,ROW_NUMBER() over (partition by num order by newid()) id from B
)
delete tb from tb,B1 where B1.num=tb.num and B1.id=tb.id;select * from B;drop table A1,B1;