利用SQL查找表中的质数(prime number)和完全数(perfect number)以及几个有趣的SQL语句

之前在某次interview中被老外问到如何用SQL找出列上的质数和完全数的问题；我当时已经多年没有写过这种考算法和SQL技巧(纯粹的技巧)的语句了，乍遇此问题倒是有些棘手。现在录以记之，供人参考.

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

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