又一道小学题的sql实现~~~

鬼谷子先生有两个绝顶聪明的门徒，一个叫孙宾（后来改名孙膑），一个叫庞涓。

有一天鬼谷子对他们说：“两个大于1而小于100的自然数相加等于一个和，相乘等于一个积。谁能猜出我说的这两个自然数是多少？”

庞涓说：“条件太少了。和积都不知道，我没有办法算。”

“好，你附耳过来。”鬼谷子悄悄地告诉庞涓和是多少。然后又悄悄地告诉孙宾积是多少。

“现在你们分别知道和积，谁能说出答案？”

庞涓想了一会，得意地说：“虽然我不知道答案是哪两个自然数，但是我知道孙宾也不知道这两个数是多少！”

孙宾淡淡一笑：“听你一说，现在我倒知道这两个数是多少了。”

庞涓大吃一惊：“慢，你先别说答案。”他又想了一会说：“师傅，我也知道这两个数是多少了。”

你知道这两个自然数是多少吗？？？

--方式1，通过逻辑推荐版
create or replace function isPrimeNum(num in number) return number is
  Result number;
begin

  result := 0;
  FOR J IN 2 .. num - 1 LOOP
    IF MOD(num, J) = 0 THEN
      result := 1;
    END IF;
  END LOOP;
  return(Result);
end isPrimeNum;

with tt as (
select lva,
       lvb,
       lva || '+' || lvb,
       lva + lvb x,
       lva || '*' || lvb,
       lva * lvb y,
       row_number() over(partition by lva + lvb, lva * lvb order by lva) rn,
       count(0) over(partition by lva * lvb) ctc,
       count(0) over(partition by lva + lvb) cts,
       case
         when (lva = 2 and isprimenum(lvb) = 1) then 0
         when (lvb = 2 and isprimenum(lva) = 1) then 0
         else 1
       end cs
  from (select level + 1 lva from dual connect by level < 50),
       (select level + 1 lvb from dual connect by level < 50)
 where (isprimenum(lva) = 0 or isprimenum(lvb) = 0 or (isprimenum(lva) = 0 and isprimenum(lvb) = 0))
)
select *
  from (select tt.*, count(0) over(partition by x) ct
          from tt
         where rn = 1
           and mod(x, 2) <> 0
           and cs = 1
           and x < 54
              --and x in(11,17,23,27,29,35,37,41,47,51,53)
           and ctc > 2
           and ctc < 6
           and isprimenum(x - 2) = 1)
 where ct = 1

--方式2  完全穷举法
with tt as (select rownum+1 col from dual connect by rownum<99)
select f.a,f.b
  from (select d.*, count(he) over(partition by he) hect2
          from (select c.*, count(ji) over(partition by ji) jict3
                  from (select b.*, min(jict) over(partition by he) jict2
                          from (select a.*,
                                       count(he) over(partition by he) hect,
                                       count(ji) over(partition by ji) jict
                                  from (select a.col a,
                                               b.col b,
                                               a.col || '+' || b.col hes,
                                               a.col + b.col he,
                                               a.col || '*' || b.col jis,
                                               a.col * b.col ji,
                                               row_number() over(partition by a.col + b.col, a.col * b.col order by a.col) rn
                                          from tt a, tt b) a
                                 where rn = 1) b
                         where hect > 1) c
                 where jict2 > 1) d
         where d.jict3 = 1) f
 where f.hect2 = 1