利用SQL查找表中的质数(prime number)和完全数(perfect number)以及几个有趣的SQL语句
之前在某次interview中被老外问到如何用SQL找出列上的质数和完全数的问题;我当时已经多年没有写过这种考算法和SQL技巧(纯粹的技巧)的语句了,乍遇此问题倒是有些棘手。现在录以记之,供人参考.
SQL> create table numbers(NO int) ; 表已创建。 SQL> insert into numbers select rownum from dba_objects; 已创建71937行。 SQL> commit; 提交完成。 SELECT X.NO as Primes /*查找质数(find prime number)*/ FROM Numbers N CROSS JOIN Numbers X WHERE mod(X.NO, N.NO) != 0 AND N.NO < X.NO GROUP BY X.NO HAVING(X.NO - Count(*)) = 2; PRIMES --------- 4931 4919 4909 4903 4889 4877 4871 4861 4831 4817 4813 ................ SELECT X.no as Perfect /*查找完全数,find perfect nober*/ FROM numbers N CROSS JOIN numbers X WHERE mod(X.no, N.no) = 0 and X.no > 1 AND N.no < X.no AND N.no > 0 GROUP BY X.no HAVING SUM(N.no) = X.no; PERFECT ---------- 6 28 496 ...................... 附: select ltrim(sys_connect_by_path(rownum || '*' || lv || '=' || /* SQL_99乘法口诀表*/ rpad(rownum * lv, 2), ' ')) from (select level lv from dual connect by level < 10) where lv = 1 connect by lv + 1 = prior lv; 1*1=1 2*2=4 2*1=2 3*3=9 3*2=6 3*1=3 4*4=16 4*3=12 4*2=8 4*1=4 5*5=25 5*4=20 5*3=15 5*2=10 5*1=5 6*6=36 6*5=30 6*4=24 6*3=18 6*2=12 6*1=6 7*7=49 7*6=42 7*5=35 7*4=28 7*3=21 7*2=14 7*1=7 8*8=64 8*7=56 8*6=48 8*5=40 8*4=32 8*3=24 8*2=16 8*1=8 9*9=81 9*8=72 9*7=63 9*6=54 9*5=45 9*4=36 9*3=27 9*2=18 9*1=9 with a as (select distinct round(a.x + b.x) x, round(a.y + b.y) y from (select (sum(x) over(order by n)) x, round(sum(y) over(order by n)) y from (select n, cos(n / 30 * 3.1415926) * 2 x, sin(n / 30 * 3.1415926) y from (select rownum - 1 n from all_objects where rownum <= 30 + 30))) a, (select n, (sum(x) over(order by n)) x, round(sum(y) over(order by n)) y from (select n, cos(m / 3 * 3.1415926) * 2 * 15 x, sin(m / 3 * 3.1415926) * 15 y from (select case when rownum <= 2 then 3 when rownum = 3 then -2 else -6 end m, rownum - 1 n from all_objects where rownum <= 5))) b) select replace (sys_connect_by_path(point, '/'), '/', null) star /*SQL 绘制奥运五环*/ 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; with a as /*sql 绘制五角星*/ (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.1415926) * 2 x, sin(trunc(n / 20) * (1 - 1 / 5) * 3.1415926) 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; SELECT LPAD(MONTH, 20 - (20 - LENGTH(MONTH)) / 2) MONTH, /*sql绘制年历*/ "Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat" FROM (SELECT TO_CHAR(dt, 'fmMonthfm YYYY') MONTH, TO_CHAR(dt + 1, 'iw') week, MAX(DECODE(TO_CHAR(dt, 'd'), '1', LPAD(TO_CHAR(dt, 'fmdd'), 2))) "Sun", MAX(DECODE(TO_CHAR(dt, 'd'), '2', LPAD(TO_CHAR(dt, 'fmdd'), 2))) "Mon", MAX(DECODE(TO_CHAR(dt, 'd'), '3', LPAD(TO_CHAR(dt, 'fmdd'), 2))) "Tue", MAX(DECODE(TO_CHAR(dt, 'd'), '4', LPAD(TO_CHAR(dt, 'fmdd'), 2))) "Wed", MAX(DECODE(TO_CHAR(dt, 'd'), '5', LPAD(TO_CHAR(dt, 'fmdd'), 2))) "Thu", MAX(DECODE(TO_CHAR(dt, 'd'), '6', LPAD(TO_CHAR(dt, 'fmdd'), 2))) "Fri", MAX(DECODE(TO_CHAR(dt, 'd'), '7', LPAD(TO_CHAR(dt, 'fmdd'), 2))) "Sat" FROM (SELECT TRUNC(SYSDATE, 'y') - 1 + ROWNUM dt FROM all_objects WHERE ROWNUM <= ADD_MONTHS(TRUNC(SYSDATE, 'y'), 12) - TRUNC(SYSDATE, 'y')) GROUP BY TO_CHAR(dt, 'fmMonthfm YYYY'), TO_CHAR(dt + 1, 'iw')) ORDER BY TO_DATE(MONTH, 'Month YYYY'), TO_NUMBER(week); MONTH Sun Mon Tue Wed Thu Fri Sat 1 1月 2010 3 4 5 6 7 8 9 2 1月 2010 10 11 12 13 14 15 16
posted on 2010-06-08 13:54 Oracle和MySQL 阅读(503) 评论(0) 编辑 收藏 举报