代码改变世界

sql 写一个查询打印所有质数小于或等于1000,打印在同一行上,用&分隔开

2017-04-19 21:20  backyyan  阅读(388)  评论(0编辑  收藏  举报

代码如下:

SELECT LISTAGG (L1, '&') WITHIN GROUP (ORDER BY L1) FROM (SELECT L1 FROM (SELECT L1, L2, RANK () OVER (PARTITION BY L1 ORDER BY L2) R FROM ( SELECT LEVEL L1 FROM DUAL CONNECT BY LEVEL <= 1000) A, ( SELECT LEVEL L2 FROM DUAL CONNECT BY LEVEL <= 1000) B WHERE L1 <> 1 AND L2 <> 1 AND MOD (L1, L2) = 0 AND L2 <= L1) WHERE R = 1 MINUS SELECT L1 FROM (SELECT L1, L2, RANK () OVER (PARTITION BY L1 ORDER BY L2) R FROM ( SELECT LEVEL L1 FROM DUAL CONNECT BY LEVEL <= 1000) A, ( SELECT LEVEL L2 FROM DUAL CONNECT BY LEVEL <= 1000) B WHERE L1 <> 1 AND L2 <> 1 AND MOD (L1, L2) = 0 AND L2 <= L1) WHERE R >= 2)