insert into b3(b1id,b2id) (select b1.id,b2.id from b1,b2 where b1.id=b2.id)
但是我不想用b1.id=b2.id,我只是想把b1.id,b2.id放到同一个表里面
INSERT INTO B3 (b1id,b2id) select b1.id,b2.id from b1,b2 ;
INSERT INTO B3 (b1id,b2id) select b1.id,b2.id from b1,b2 where b1.rownum=b2.rownum
b1.rownum操作时提示没有这个表,视图,好象rownum不是一个表的属性
INSERT INTO B3 (b1id,b2id) select b1.id,b2.id from b1,b2
先比較一下是b1.id 大還是b2,id大,如果是b1.id大可以這樣寫 select b1.id,b1.number,b2.id,b2.number from b1 left join b2 on(b1.id=b2.id) 如果是b2.id大可以這樣寫 select b1.id,b1.number,b2.id,b2.number from b2 left joinb1 on (b2.id=b1.id)
INSERT INTO B3 (b1id,b2id) select B1, B2 from (select b1.id B1,b2.id B2 from b1,b2) where B1 is not null and B2 is not null;
假设 b2的记录比b1的记录少: insert into b3(b1id,b2id) select a.id,b.id from (select rownum r1,id from b1) a (select rownum r2,id from b2) b where r1 = r2(+)
在执行是能够用性能管理器看看SQL的性能以提高工作效率。
下面语句能实现,不管是b1记录多还是b2记录多 insert into b3 select t.b1,t.b2 from (select row_num,max(b1) as b1,max(b2) as b2 from (select rownum as row_num,b1 as b1, to_number(null) as b2 from b1 union all select rownum as row_num,to_number(null) as b1,b2 as b2 from b2) group by row_num) t;
create table b1 (b1 numeric(2,0)); create table b2 (b2 numeric(2,0)); create table b3 (b1 numeric(2,0),b2 numeric(2,0));insert into b3(b1, b2) select b1,null from b1 union select null,b2 from b2 ;
b3表里的两个字段有关系吗?一条记录里既包含id1又包含id2还是两个字段独立?
insert into b3(b1, b2) select a.v_c, b.v_c from (select rownum v_r, c.v_c from ( (select b1 v_c from b1 where (select count(*) from b1) > (select count(*) from b2) ) union (select b2 v_c from b2 where (select count(*) from b1) <= (select count(*) from b2) ) ) c ) a , (select rownum v_r, d.v_c from ( (select b1 v_c from b1 where (select count(*) from b1) < (select count(*) from b2) ) union (select b2 v_c from b2 where (select count(*) from b1) >= (select count(*) from b2) ) ) d ) b where a.v_r = b.v_r(+) ;
onejune4450(中文字符) 的方法已经能体现我的要求了,就是其中没有对两个表中能够建立关系(=)的记录放在一条记录里,比如:可能是(8,3),(4,8),要是能改为(8,8)就好了, 表b1,b2里的字段id,有的是相等的,有的不相等,我希望能把相等的放在一起,不相等的也放在一起,这样好比较, TO verybigmouthz(大嘴智) 你的方法我后面试一试。非常感谢各位的帮忙,在此谢过了:).
不好意思,再次路过时才发现先前对题意并未完全理解,SQL语句应改为 insert into b3(b1, b2) select (case when (select count(*) from b1) > (select count(*) from b2) then a.v_c else b.v_c end), (case when (select count(*) from b1) <= (select count(*) from b2) then b.v_c else a.v_c end) from (select rownum v_r, c.v_c from ( (select b1 v_c from b1 where (select count(*) from b1) > (select count(*) from b2) ) union (select b2 v_c from b2 where (select count(*) from b1) <= (select count(*) from b2) ) ) c ) a , (select rownum v_r, d.v_c from ( (select b1 v_c from b1 where (select count(*) from b1) <= (select count(*) from b2) ) union (select b2 v_c from b2 where (select count(*) from b1) > (select count(*) from b2) ) ) d ) b where a.v_r = b.v_r(+) ; ------------------------------------------------------------------------ 其实大可不必这样做,用一条SQL语句实现其难度太大, 如果 要完全实现shukuangren(蜀狂人) 所补叙的要求,我认为则可以利用plsql编程实现 利用游标是很好实现的. 再者在数据库的设计上也可以做些重组考虑,一个很简单的问题何必要做得这样复杂呢?不过做为练习这样也未尝不可.呵呵 希望有机会和大家一起学习.
to verybigmouthz(大嘴智) 非常感谢,不过呢你也说的对,用一条SQL语句好象是太过分了一点呀:)。 我自己也用编程做了一个,还是用编程做的思路比较清楚。 你写的语句我会好好看一看的。 谢过啦以后共同进步。
(select b1.id,b2.id from b1,b2 where b1.id=b2.id)
select b1.id,b1.number,b2.id,b2.number from b1 left join b2 on(b1.id=b2.id)
如果是b2.id大可以這樣寫
select b1.id,b1.number,b2.id,b2.number from b2 left joinb1 on (b2.id=b1.id)
vrv0129() 其实我要的东东是在full join的基础上做改动,把那些没有匹配的匹配起来,匹配的规则可以是任意的.比如:b1中有4,而b2中有5都没有匹配,那么就可以把他们匹配起来,如果,最后b1的都匹配了,而b2中还有6没有匹配,那么就可以把6和一个null存到一起.我现在在做一个程序来实现,只是在其中遇到了一个问题,就是关于到底有多少不能匹配的,而b1,b2各有多少,还有就是如何分配数据结构来存储.有劳大家了.
(select b1.id B1,b2.id B2 from b1,b2)
where B1 is not null and B2 is not null;
insert into b3(b1id,b2id)
select a.id,b.id
from (select rownum r1,id from b1) a
(select rownum r2,id from b2) b
where r1 = r2(+)
insert into b3
select t.b1,t.b2 from
(select row_num,max(b1) as b1,max(b2) as b2 from
(select rownum as row_num,b1 as b1,
to_number(null) as b2 from b1
union all
select rownum as row_num,to_number(null) as b1,b2 as b2 from b2)
group by row_num) t;
create table b1 (b1 number);
create table b1 (b1 number);
create table b3 (b1 number,b2 number);
有什么问题吗?
create table b2 (b2 numeric(2,0));
create table b3 (b1 numeric(2,0),b2 numeric(2,0));insert into b3(b1, b2)
select b1,null from b1
union
select null,b2 from b2
;
select a.v_c, b.v_c
from
(select rownum v_r, c.v_c from
(
(select b1 v_c from b1
where (select count(*) from b1) > (select count(*) from b2)
)
union
(select b2 v_c from b2
where (select count(*) from b1) <= (select count(*) from b2)
)
) c
) a
,
(select rownum v_r, d.v_c from
(
(select b1 v_c from b1
where (select count(*) from b1) < (select count(*) from b2)
)
union
(select b2 v_c from b2
where (select count(*) from b1) >= (select count(*) from b2)
)
) d
) b
where a.v_r = b.v_r(+)
;
以上是在 onejune4450(中文字符) 的基础上利用union 返回不同数据集.
进行虚拟 a, b表的自动转换功能(即 a表始终是行数最多的表内容
b表始终是行数最少的表内容)
我已经在oracle上测试过,不管b1,还是b2表的数据多, 都可以达到以上目的.
表b1,b2里的字段id,有的是相等的,有的不相等,我希望能把相等的放在一起,不相等的也放在一起,这样好比较,
TO verybigmouthz(大嘴智) 你的方法我后面试一试。非常感谢各位的帮忙,在此谢过了:).
insert into b3(b1, b2)
select
(case when (select count(*) from b1) > (select count(*) from b2)
then a.v_c else b.v_c end),
(case when (select count(*) from b1) <= (select count(*) from b2)
then b.v_c else a.v_c end)
from
(select rownum v_r, c.v_c from
(
(select b1 v_c from b1
where (select count(*) from b1) > (select count(*) from b2)
)
union
(select b2 v_c from b2
where (select count(*) from b1) <= (select count(*) from b2)
)
) c
) a
,
(select rownum v_r, d.v_c from
(
(select b1 v_c from b1
where (select count(*) from b1) <= (select count(*) from b2)
)
union
(select b2 v_c from b2
where (select count(*) from b1) > (select count(*) from b2)
)
) d
) b
where a.v_r = b.v_r(+)
;
------------------------------------------------------------------------
其实大可不必这样做,用一条SQL语句实现其难度太大,
如果 要完全实现shukuangren(蜀狂人) 所补叙的要求,我认为则可以利用plsql编程实现
利用游标是很好实现的.
再者在数据库的设计上也可以做些重组考虑,一个很简单的问题何必要做得这样复杂呢?不过做为练习这样也未尝不可.呵呵
希望有机会和大家一起学习.
非常感谢,不过呢你也说的对,用一条SQL语句好象是太过分了一点呀:)。
我自己也用编程做了一个,还是用编程做的思路比较清楚。
你写的语句我会好好看一看的。
谢过啦以后共同进步。