CREATE TABLE TABLE2 AS
SELECT TAB1_A A,TAB1_B||TAB2_B||TAB3_B B FROM
( SELECT TAB1.ID TAB1_ID,TAB1.A TAB1_A,TAB1.B TAB1_B,
TAB2.ID TAB2_ID,TAB2.A TAB2_A,TAB2.B TAB2_B,
TAB3.ID TAB3_ID,TAB3.A TAB3_A,TAB3.B TAB3_B
FROM
(SELECT ROWNUM ID,A,B FROM TABLE1 ) TAB1,
(SELECT ROWNUM ID,A,B FROM TABLE1 ) TAB2,
(SELECT ROWNUM ID,A,B FROM TABLE1 ) TAB3
)
WHERE TAB1_B < TAB2_B AND TAB2_B <TAB3_B
AND TAB1_A = TAB2_A AND TAB1_A = TAB3_A
SELECT TAB1_A A,TAB1_B||TAB2_B||TAB3_B B FROM
( SELECT TAB1.ID TAB1_ID,TAB1.A TAB1_A,TAB1.B TAB1_B,
TAB2.ID TAB2_ID,TAB2.A TAB2_A,TAB2.B TAB2_B,
TAB3.ID TAB3_ID,TAB3.A TAB3_A,TAB3.B TAB3_B
FROM
(SELECT ROWNUM ID,A,B FROM TABLE1 ) TAB1,
(SELECT ROWNUM ID,A,B FROM TABLE1 ) TAB2,
(SELECT ROWNUM ID,A,B FROM TABLE1 ) TAB3
)
WHERE TAB1_B < TAB2_B AND TAB2_B <TAB3_B
AND TAB1_A = TAB2_A AND TAB1_A = TAB3_A
解决方案 »
- SQLSERVER时间查询语句转换oracle时间查询语句
- 用户与授权的问题:从安全和开发的角度上,使用一个用于项目管理的用户,建立了表和存储过程等,但在生产环境,想使用另外一个用户(,只具备存储过程执行权限)
- 顺序插入语句
- 关于oracle查询日期横向显示的问题
- 配置logminer时发生ORA-01284错误为什么啊?
- 关于储存过程varchar2类型的为操作的问题?
- 超级笨的问题。大家来看,估计很容易,是我对ORACLE不熟悉。100分。
- oracle9i在那里改日期的显示格式?
- 这条sql语句应该怎么写?
- Oracle启动
- 请教简单的SQL问题
- 如何在ORACLE中插入从年到毫秒的数据???
STR2 VHARCHAR2(?);
Value1 VHARCHAR2(?);
.........
STR1 :=' ';
STR2 :=' ';
DECLARE CURSOR curTemp IS SELECT DISTINCT m FROM table1;
BEGIN
OPEN curTemp;
LOOP
FETCH curTemp INTO Value1;
EXIT WHEN curTemp%NOTFOUND;
SELECT N INTO STR1 FROM table1 WHERE M= Value1;
STR2:=STR1 +STR2;
END LOOP;
CLOSE curTemp;
END;
INSERT INTO table2 values(m, str2);
其实没用,也忘了删了,这是去掉rownum后的sqlSELECT TAB1_A A,TAB1_B||TAB2_B||TAB3_B B FROM
( SELECT TAB1.A TAB1_A,TAB1.B TAB1_B,
TAB2.A TAB2_A,TAB2.B TAB2_B,
TAB3.A TAB3_A,TAB3.B TAB3_B
FROM
(SELECT A,B FROM TABLE1 ) TAB1,
(SELECT A,B FROM TABLE1 ) TAB2,
(SELECT A,B FROM TABLE1 ) TAB3
)
WHERE TAB1_B < TAB2_B AND TAB2_B <TAB3_B
AND TAB1_A = TAB2_A AND TAB1_A = TAB3_A
然后
select distinct m , f(m) from table1 ;
ok
cursor c1 is select n from table1 where m = v_m;
v_n varchar2(100);
begin
for i in c1 loop
v_n := v_n || i.n;
end loop;
return v_n ;
end;
/
整理如下,各位朋友可以测试:
DROP TABLE table1;
CREATE TABLE table1(m VARCHAR2(10),n VARCHAR2(10));
INSERT INTO table1 VALUES('a','x');
INSERT INTO table1 VALUES('a','y');
INSERT INTO table1 VALUES('a','z');
INSERT INTO table1 VALUES('b','u');
INSERT INTO table1 VALUES('b','v');
INSERT INTO table1 VALUES('b','w');
create or replace function f(v_m varchar2) return varchar2 is
cursor c1 is select n from table1 where m = v_m;
v_n varchar2(100);
begin
for i in c1 loop
v_n := v_n || i.n;
end loop;
return v_n ;
end;
/
select distinct m , f(m) from table1;