ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 又一道小学题的sql实现~~~

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

原创 Linux操作系统 作者:junsansi 时间:2008-01-24 18:04:50 0 删除 编辑

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

有一天鬼谷子对他们说:“两个大于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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-21

  • 博文量
    640
  • 访问量
    4094188