select t1.f1||t2.f1||t3.f1 from t1,t2,t3 where t1.f1!=t2.f1 and t1.f1!=t3.f1 and t2.f1!=t3.f1
select f1 from t1 union select f1 from t2 union select f1 from t3;
对不起,我只是用别人写好的工具在提数据,好像只能用通常的select t1.f1||t2.f1||t3.f1 from t1,t2,t3 where t1.f1!=t2.f1 and t1.f1!=t3.f1 and t2.f1!=t3.f1 提出来的是'cccbbbaaa'
select f1 from t1 union select f1 from t2 union select f1 from t3; 提出来的还是有重复,怎么解决? 新手请帮忙
create table cc as select f1 from t1 union select f1 from t2 union select f1 from t3;create function name_f(p_table in varchar2) return varchar2 as str varchar2(60); v_f1 t1.f1%type; type t_sor is ref cursor; v_sor t_sor; begin str open v_sor for 'select * from '||p_table; loop fetch v_sor into v_f1; exit when v_sor%notfound; str:=str||v_f1; end loop; close v_sor; return str; end; /select name_f('cc') from dual;
open v_sor for 'select distinct f1 from '||p_table;
DECLARE TYPE ComposedTable IS TABLE OF t1.f1%TYPE INDEX BY BINARY_INTEGER; v_ComposedTable ComposedTable; v_counter NUMBER DEFAULT 1; v_temp t1.f1%TYPE;
CURSOR c_t1_distinct IS SELECT DISTINCT t1.f1 FROM t1;
CURSOR c_t2_individual IS SELECT DISTINCT t2.f1 FROM t2 WHERE t2.f1 NOT IN (SELECT t1.f1 FROM t1) AND t2.f1 NOT IN (SELECT t3.f1 FROM t3);
CURSOR c_t3_individual IS SELECT DISTINCT t3.f1 FROM t3 WHERE t3.f1 NOT IN (SELECT t1.f1 FROM t1) AND t3.f1 NOT IN (SELECT t2.f1 FROM t2);
CURSOR c_t2_t3_comp_individual IS SELECT DISTINCT t2.f1 FROM t2 WHERE t2.f1 IN (SELECT t3.f1 FROM t3) AND t2.f1 NOT IN (SELECT t1.f1 FROM t1); BEGIN FOR v_pointer IN c_t1_distinct LOOP v_ComposedTable(v_counter) := v_pointer.f1; v_counter := v_counter + 1; END LOOP;
OPEN c_t2_individual; LOOP FETCH c_t2_individual INTO v_temp; EXIT WHEN c_t2_individual%NOTFOUND; v_ComposedTable(v_counter) := v_temp; v_counter := v_counter + 1; END LOOP; CLOSE c_t2_individual;
OPEN c_t3_individual; LOOP FETCH c_t3_individual INTO v_temp; EXIT WHEN c_t3_individual%NOTFOUND; v_ComposedTable(v_counter) := v_temp; v_counter := v_counter + 1; END LOOP; CLOSE c_t3_individual;
OPEN c_t2_t3_comp_individual; LOOP FETCH c_t2_t3_comp_individual INTO v_temp; EXIT WHEN c_t2_t3_comp_individual%NOTFOUND; v_ComposedTable(v_counter) := v_temp; v_counter := v_counter + 1; END LOOP; CLOSE c_t2_t3_comp_individual;
FOR v_c IN 1..(v_counter - 1) LOOP DBMS_OUTPUT.PUT_LINE(v_ComposedTable(v_c)); DBMS_OUTPUT.PUT_LINE('*****************'); END LOOP; END;用这个过程Scripe可以实现你的要求, 如果单纯的SQL,那么它应该支持FULL JOIN才能实现。
t1.f1中可能是aaa ,bbb,ccc,ddd
t2.f1中可能是bbb,ddd,eee,ggg
t3.f1中可能是ggg,fff,www用你的得到的是'aaaeeefff'
我是要aaa,bbb,ccc,ddd,eee,ggg,fff,www
from t1,t2,t3
where t1.f1!=t2.f1
and t1.f1!=t3.f1
and t2.f1!=t3.f1
union
select f1 from t2
union
select f1 from t3;
from t1,t2,t3
where t1.f1!=t2.f1
and t1.f1!=t3.f1
and t2.f1!=t3.f1
提出来的是'cccbbbaaa'
union
select f1 from t2
union
select f1 from t3; 提出来的还是有重复,怎么解决?
新手请帮忙
select f1 from t1
union
select f1 from t2
union
select f1 from t3;create function name_f(p_table in varchar2)
return varchar2
as
str varchar2(60);
v_f1 t1.f1%type;
type t_sor is ref cursor;
v_sor t_sor;
begin
str
open v_sor for 'select * from '||p_table;
loop
fetch v_sor into v_f1;
exit when v_sor%notfound;
str:=str||v_f1;
end loop;
close v_sor;
return str;
end;
/select name_f('cc') from dual;
而且好像不能建表。我是在个别人的库里提,除了select 不能干其他的。
如果是光select无法作,请告诉一声,谢了。
TYPE ComposedTable IS TABLE OF t1.f1%TYPE
INDEX BY BINARY_INTEGER;
v_ComposedTable ComposedTable;
v_counter NUMBER DEFAULT 1;
v_temp t1.f1%TYPE;
CURSOR c_t1_distinct IS
SELECT DISTINCT t1.f1 FROM t1;
CURSOR c_t2_individual IS
SELECT DISTINCT t2.f1
FROM t2
WHERE t2.f1 NOT IN (SELECT t1.f1 FROM t1)
AND t2.f1 NOT IN (SELECT t3.f1 FROM t3);
CURSOR c_t3_individual IS
SELECT DISTINCT t3.f1
FROM t3
WHERE t3.f1 NOT IN (SELECT t1.f1 FROM t1)
AND t3.f1 NOT IN (SELECT t2.f1 FROM t2);
CURSOR c_t2_t3_comp_individual IS
SELECT DISTINCT t2.f1
FROM t2
WHERE t2.f1 IN (SELECT t3.f1 FROM t3)
AND t2.f1 NOT IN (SELECT t1.f1 FROM t1);
BEGIN
FOR v_pointer IN c_t1_distinct LOOP
v_ComposedTable(v_counter) := v_pointer.f1;
v_counter := v_counter + 1;
END LOOP;
OPEN c_t2_individual;
LOOP
FETCH c_t2_individual INTO v_temp;
EXIT WHEN c_t2_individual%NOTFOUND;
v_ComposedTable(v_counter) := v_temp;
v_counter := v_counter + 1;
END LOOP;
CLOSE c_t2_individual;
OPEN c_t3_individual;
LOOP
FETCH c_t3_individual INTO v_temp;
EXIT WHEN c_t3_individual%NOTFOUND;
v_ComposedTable(v_counter) := v_temp;
v_counter := v_counter + 1;
END LOOP;
CLOSE c_t3_individual;
OPEN c_t2_t3_comp_individual;
LOOP
FETCH c_t2_t3_comp_individual INTO v_temp;
EXIT WHEN c_t2_t3_comp_individual%NOTFOUND;
v_ComposedTable(v_counter) := v_temp;
v_counter := v_counter + 1;
END LOOP;
CLOSE c_t2_t3_comp_individual;
FOR v_c IN 1..(v_counter - 1) LOOP
DBMS_OUTPUT.PUT_LINE(v_ComposedTable(v_c));
DBMS_OUTPUT.PUT_LINE('*****************');
END LOOP;
END;用这个过程Scripe可以实现你的要求,
如果单纯的SQL,那么它应该支持FULL JOIN才能实现。