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

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

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

--方式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);

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)
)
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

• 博文量
640
• 访问量
4094188