mysql质数_利用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);

MONTHSunMonTueWedThuFriSat

1 1月 2010 3 4 5 6 7 8 9

2 1月 201010111213141516