之前在某次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
Comment