又一道小學題的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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7607759/viewspace-160017/，如需轉載，請註明出處，否則將追究法律責任。