回复 wangfans 高难度sql,喜欢研究SQL算法的进

nyfor · 发表于 2008-3-6 22:52

回复 http://www.itpub.net/viewthread.php?tid=949571&page=1
原帖问题由 wangfans 发布:

高难度sql,喜欢研究SQL算法的进

SQL> select * from test3;

      ID    VALUE    POWER
---------- ---------- ----------
      1       2       3
      2       1       2
      3       4       4
      4       5       2
      5       4       3


ID为主键，value代表值，power代表权值。

要求：

求出所有值乘以比相应权值小的整数之积为16的权值组合。

例如：
假如test3的所有数据如上，则

2*1+1*1+4*1+5*1+4*1=16(这里权值组合为  1，1，1，1)
2*1+1*1+4*0+5*1+4*2=16(这里权值组合为  1,1,0,1,2)

练了一下手(有点成就感,所以开了新帖

  ):
以下测试环境 Oracle 10G
create table t(id number,value number,power number);
insert into T values (1, 2, 3);
insert into T values (2, 1, 2);
insert into T values (3, 4, 4);
insert into T values (4, 5, 2);
insert into T values (5, 4, 3);
commit;
with tmp as
(
  select level lev, sys_connect_by_path(value || '*' || rn, '+') powers, value * rn val
from (select id,value,rn
         from t, (select rownum - 1 rn from t) a
         start with rn = 0
         connect by prior id = id
               and rn = prior rn + 1
               and rn < power)
start with id = 1
  connect by id = prior id + 1
)
select ltrim(a.powers, '+') powers
  from tmp a, tmp b
where a.powers || '+'  like b.powers || '+%'
and a.lev = (select count(1) from t)
group by a.powers
having sum(b.val) = 16;

POWERS
----------------------
2*2+1*0+4*1+5*0+4*2
2*0+1*0+4*2+5*0+4*2
2*0+1*0+4*3+5*0+4*1
2*2+1*0+4*2+5*0+4*1
2*2+1*0+4*3+5*0+4*0
2*1+1*1+4*0+5*1+4*2
2*1+1*1+4*1+5*1+4*1
2*1+1*1+4*2+5*1+4*0

8 rows selected

SQL>

不过这个SQL要完成这个功能, 还需要对数据有个限制, 就是 ID 字段的值是从 1 开始连续不断的.
以上SQL在10G环境中运行通过. 此 SQL 也没使用到 10G 的什么新特性, 但是跑到 9i 环境中运行失败.

在考虑 ID 字段值不是从 1 开始并连续的情况下, 内嵌一个 SQL 查询来自行生成一个连续的 ID 时有些问题.

不断的尝试SQL写法, 终于发现一种可以解决ID字段不是从1开始并连续的问题了. 如下:
with tmp as
(
  select level lev, sys_connect_by_path(value || '*' || rn, '+') powers, value * rn val
from (select idx, value, rn
         from (select rn, t.id, idx, value, power
                  from t, (select rownum - 1 rn from t) a, (select rownum idx, id from t) b
                  where t.id = b.id)
         start with rn = 0
         connect by prior id = id
               and rn = prior rn + 1
               and rn < power)
start with idx = 1
  connect by idx = prior idx + 1
)
select ltrim(a.powers, '+') powers
  from tmp a, tmp b
where a.powers || '+' like b.powers || '+%'
and a.lev = (select count(1) from t)
group by a.powers
having sum(b.val) = 16;

[ 本帖最后由 nyfor 于 2008-3-7 08:51 编辑 ]