这道题能用SQL解？

〇〇

with t as(select power(2,level-1)a from dual connect by level<=ceil(log(2,1000000000)))
,r as(select a.a+b.a+c.a+d.a+e.a v
from t a,t b,t c,t d,t e
where a.a<b.a and b.a<c.a and c.a<d.a and d.a<e.a),
r1 as(select v rv,lag(v)over(order by v)lv,sum(v)over(order by v)ss from r),
test as(select trunc(dbms_random.value(1,1000000000)) r1,
trunc(dbms_random.value(1,1000000000)) r2
from dual connect by level<=50),
test1 as(select rownum rn,least(r1,r2)r1,greatest(r1,r2)r2 from test),
a as(select rn,r1,rv,ss from r1,test1 where r1 between lv and rv),
b as(select rn,r2,rv,ss from r1,test1 where r2 between lv and rv)
select a.rn,r1,r2,a.rv,b.ss-a.ss from a,b where a.rn=b.rn
;

已选择50行。

已用时间:  00: 00: 22.57

newkid

同样的思路如果写成代码效率就更好，因为SQL只能傻傻做笛卡尔积然后过滤，不知道访问一遍排序的结果就可以得到答案。

〇〇

newkid 发表于 2016-10-17 22:27
同样的思路如果写成代码效率就更好，因为SQL只能傻傻做笛卡尔积然后过滤，不知道访问一遍排泄的结果就可以 ...

类似这样?
int calc(int x, int y)
{
long long n[32];
long long k=0;
long long l=0;
int m=0;
for(int i=0;i<32;i++)
n[ i ]=1LL<<i;
//printf("%lld\n",n[31]);
int len=ceil(log(y)/log(2));
for(int a=0;a<len;a++)
for(int b=0;b<a;b++)
for(int c=0;c<b;c++)
for(int d=0;d<c;d++)
for(int e=0;e<d;e++)
{
k=n[a]+n[ b ]+n[c]+n[d]+n[e];
if(m==0 && k>=x)
m=k;
if(k>=x && k<=y)
{
//printf("%d:%d,%d,%d,%d,%d,%lld\n",cnt,a,b,c,d,e,k);
l=l+k;
}
}

printf("(%d-%d)min=%d,sum=%lld\n",x,y,m,l);
return 0;
}