表A只有1个字段FA,而有4000以上的记录,如
FA
1
2
3
...
4333表B只有1个字段FB,而有100以内的记录,如
1
2
...
11
现在要把表B的记录按顺序分配给表A,同时插进表C
使得表C结果如下
FA FB
1 1
2 2
.. ..
11 11
12 1
13 2
.. ..该怎么处理可以达到这种效果
FA
1
2
3
...
4333表B只有1个字段FB,而有100以内的记录,如
1
2
...
11
现在要把表B的记录按顺序分配给表A,同时插进表C
使得表C结果如下
FA FB
1 1
2 2
.. ..
11 11
12 1
13 2
.. ..该怎么处理可以达到这种效果
FA从1到4333
FB从1到11其中被除数是字段FA,除数是B表的记录行数。
from (select * from A left join B on A.FA is not null) D
insert into C select * from A left join B on A.FA is not null
on case (FA%11) when 0 then 11 else (FA%11) end=FB
--建立測試環境
Create Table A
(FA Int)
Insert A Select 1
Union All Select 2
Union All Select 3
Union All Select 4
Union All Select 5
Union All Select 6
Union All Select 7
Union All Select 8
Union All Select 9
Union All Select 10Create Table B
(FB Int)
Insert B Select 1
Union All Select 2
Union All Select 3Create Table C
(FA Int,
FB Int)
GO
--測試
Insert C Select A.FA,B.FB From A Inner Join B On (A.FA-B.FB)%(Select Max(FB) From B)=0 Order By FASelect *From C Order By FA
GO
--刪除測試環境
Drop Table A,B,C
--結果
/*
FA FB
1 1
2 2
3 3
4 1
5 2
6 3
7 1
8 2
9 3
10 1
*/
(FA Int)
Insert A Select 1
Union All Select 2
Union All Select 3
Union All Select 4
Union All Select 5
Union All Select 6
Union All Select 7
Union All Select 8
Union All Select 9
Union All Select 10Create Table B
(FB Int)
Insert B Select 1
Union All Select 2
Union All Select 3select FA, case when FA%(select max(FB) from B)>0 then FA%(select max(FB) from B) else (select max(FB) from B) end as FB
from A
insert into aa values(1);
insert into aa values(2);
insert into aa values(3);
insert into aa values(4);
insert into aa values(5);create table bb(id number,name varchar2(10));
insert into bb values(1,'a');
insert into bb values(2,'b');
create tabke cc(id1 number,id2 number,name varchar2(10));declare
count_bb number;
count_aa number;
k number:=1;
m number:=1;
begin
select count(*) into count_aa from aa;
select count(*) into count_bb from bb;
for i in 1..floor(count_aa/count_bb) loop
insert into cc as select m,id,name from bb;
m:=m+1;
end loop; for i in (select * from bb) loop
insert into cc as select m,i.id,i.name from bb;
m:=m+1;
k:=k+1;
exit when k>mod(count_aa,count_bb);
end loop;
end;
得到结果:
1 1 a
2 2 b
3 1 a
4 2 b
5 1 a