Oracle with的使用

SQL查询技巧与全排列示例

最新推荐文章于 2024-02-28 23:41:33 发布

原创最新推荐文章于 2024-02-28 23:41:33 发布 · 897 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #insert #integer #null #sql #function

oracle 专栏收录该内容

64 篇文章

订阅专栏

create table yl(id integer,yorder integer);

插入记录：

SQL> insert into yl values(1,null);

1 row inserted

SQL> insert into yl values(3,null);

1 row inserted

SQL> insert into yl values(2,null);

1 row inserted

SQL> commit;

SQL> select * from yl;

                                     ID                                  YORDER
--------------------------------------- ---------------------------------------
                                      1
                                      3
                                      2

SQL> insert into yl values(100,null);

1 row inserted

SQL> commit;

SQL> select y.*,rownum from (select * from yl order by id) y;

                                     ID                                  YORDER     ROWNUM
--------------------------------------- --------------------------------------- ----------
                                      1                                                  1
                                      2                                                  2
                                      3                                                  3
                                    100                                                  4

查询id，不是顺序号的

select y.*,rownum rn from (select * from yl order by id) y
with a as (select y.*,rownum rn from (select * from yl order by id) y)
select * from a,a b where a.id!=b.rn and a.id=b.id;

ID YORDER RN ID YORDER RN
--------------------------------------- --------------------------------------- ---------- --------------------------------------- --------------------------------------- ----------
100 4 100

另外的实例

实现自然数N的全排列

n=3;

WITH A AS (SELECT ROWNUM ID FROM DUAL CONNECT BY LEVEL <= 3)
SELECT A.ID || B.ID || C.ID
FROM A, A B, A C
WHERE A.ID != B.ID
AND A.ID != C.ID
AND B.ID != C.ID
;

n=4;

WITH A AS (SELECT ROWNUM ID FROM DUAL CONNECT BY LEVEL <= 4)
SELECT A.ID || B.ID || C.ID || D.ID
FROM A, A B, A C, A D
WHERE A.ID != B.ID
AND A.ID != C.ID
AND A.ID != D.ID
AND B.ID != C.ID
AND B.ID != D.ID
AND C.ID != D.ID;

CREATE OR REPLACE FUNCTION F_TEST(P_IN NUMBER) RETURN SYS_REFCURSOR AS
V_STR    VARCHAR2(32767);
V_RETURN SYS_REFCURSOR;
BEGIN
V_STR := 'WITH A AS (SELECT ROWNUM ID FROM DUAL CONNECT BY LEVEL <= ' || P_IN ||
           ') SELECT ';
FOR I IN 1 .. P_IN LOOP
    V_STR := V_STR || 'A' || I || '.ID || ';
END LOOP;
V_STR := RTRIM(V_STR, '| ') || ' FROM ';
FOR I IN 1 .. P_IN LOOP
    V_STR := V_STR || 'A A' || I || ', ';
END LOOP;
V_STR := RTRIM(V_STR, ', ') || ' WHERE ';
FOR I IN 1 .. P_IN LOOP
    FOR J IN I + 1 .. P_IN LOOP
      V_STR := V_STR || 'A' || I || '.ID != A' || J || '.ID AND ';
    END LOOP;
END LOOP;
OPEN V_RETURN FOR SUBSTR(V_STR, 1, LENGTH(V_STR) - 4);

RETURN V_RETURN;
END;