sql patindex oracle,oralce的sql语句图案

大家好，我是正在学习oralce的一名同志，这个代码执行出的是一颗五角星，说实话不是我写的，一名开发人员给的，如果把他研究明白了，会有不少收获呢！

代码：

with a as ( select distinct round(sum(x) over(order by n)) x, round(sum(y) over(order by n)) y from (select n, cos(trunc(n / 20) * (1-1/5) * 3.14159265358979323846) * 2 x, sin(trunc(n / 20) * (1-1/5) * 3.14159265358979323846) y from (select rownum - 1 n from all_objects where rownum <= 20 * 5)) )select replace(sys_connect_by_path(point, '/'), '/', null) star from (select b.y, b.x, decode(a.x, null, ' ', '*') point from a, (select * from (select rownum - 1 + (select min(x) from a) x from all_objects where rownum <= (select max(x) - min(x) + 1 from a)), (select rownum - 1 + (select min(y) from a) y from all_objects where rownum <= (select max(y) - min(y) + 1 from a))) b where a.x(+) = b.x and a.y(+) = b.y)where x = (select max(x) from a)start with x = (select min(x) from a)connect by y = prior y and x = prior x + 1;

显示的图案为：

SQL> with a as ( select distinct round(sum(x) over(order by n)) x, round(sum(y) over(order by n)) y from (select n, cos(trunc(n / 20) * (1-1/5) * 3.14159265358979323846) * 2 x, sin(trunc(n / 20) * (1-1/5) * 3.14159265358979323846) y from (select rownum - 1 n from all_objects where rownum <= 20 * 5)) )select replace(sys_connect_by_path(point, '/'), '/', null) star from (select b.y, b.x, decode(a.x, null, ' ', '*') point from a, (select * from (select rownum - 1 + (select min(x) from a) x from all_objects where rownum <= (select max(x) - min(x) + 1 from a)), (select rownum - 1 + (select min(y) from a) y from all_objects where rownum <= (select max(y) - min(y) + 1 from a))) b where a.x(+) = b.x and a.y(+) = b.y)where x = (select max(x) from a)start with x = (select min(x) from a)connect by y = prior y and x = prior x + 1;

STAR

------------------------------------------------------------------------------------------------------------------------------------------------------

*

* *

* *

*   *

**   **

*       *

*       *

* * * * * * * *** * * * *** * * * * * * *

**          *           *          **

**       *           *       **

*    *             *    *

** *             * **

** *         * **

*    * *   * *    *

*       * *       *

*      * * * *      *

*    * *       * *    *

*  *               *  *

** *                 * **

*                       *

20 rows selected.

Elapsed: 00:00:00.65

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29479238/viewspace-1080185/，如需转载，请注明出处，否则将追究法律责任。