SQL算法--如果显示记录中某字段之和最接近某值的所有记录

Question

  with T1  as
   (
   select 1 T1_01,4.5 T1_02 from dual union all
   select 2 T1_01,5.1 T1_02 from dual union all
   select 3 T1_01, 5 T1_02 from dual union all
   select 4 T1_01,7.2 T1_02 from dual union all
   select 5 T1_01,4.23 T1_02 from dual union all
   select 6 T1_01,20 T1_02 from dual union all
   select 7 T1_01,30 T1_02 from dual union all 
   select 8 T1_01,1 T1_02 from dual   
   )
   select a.*
   from T1 a,
           (select t4.col1, t4.col3
              from (select t3.*, row_number() over(order by col5) rn
                      from (select t1.T1_01 col1,
                                   t1.T1_02 col2,
                                   t2.T1_01 col3,
                                   t2.T1_02 col4,
                                   abs(11 - (t2.T1_02 + t1.T1_02)) col5   /* 显示最接近11的所有记录 */
                               from T1 t1, T1 t2) t3
                     where t3.col1 != t3.col3) t4
             where t4.rn = 1) b
      where a.T1_01 = b.col1
      or a.T1_01 = b.col3;          
 
--上面的查询结果显示的是
     T1_01      T1_02
---------- ----------
         3          6
         2        5.1
         
--不是我要的结果，上面的查询SQL只能对两条记录之间进行合计，显示合计最接近11的记录应该是：
     T1_01      T1_02
---------- ----------
         3          5
         2        5.1
         8        1                  
 应该如何改写这个SQL算法？

Answer 1

也就是说，我贴出来的SQL不是我所要的结果，需要修改。
我所要的结果是:
在表T1中查找T1_02字段之和最接近11的所有记录。
当然上面列举的T1中的数据是举例，实际中，T1中的数据有很多，T1_02字段的值也没任何规律。根据上面我T1中的数据，正确的结果应该是显示下面这三条记录。     T1_01      T1_02
---------- ----------
         3          5
         2        5.1
         8        1      因为 5 + 5.1 + 1 =11.1 （最接近11）。

Answer 2

  with T1  as
   (
   select 1 T1_01,4.5 T1_02 from dual union all
   select 2 T1_01,5.1 T1_02 from dual union all
   select 3 T1_01, 5 T1_02 from dual union all
   select 4 T1_01,7.2 T1_02 from dual union all
   select 5 T1_01,4.23 T1_02 from dual union all
   select 6 T1_01,20 T1_02 from dual union all
   select 7 T1_01,30 T1_02 from dual union all 
   select 8 T1_01,1 T1_02 from dual   
   )
,tmp as(
select substr(max(sys_connect_by_path(t1_01, ',')), 2) id,
       func_sum(substr(max(sys_connect_by_path(t1_02, '+')), 2)) num
  from t1
connect by prior t1_01 < t1_01
 group by rownum - level
)
select *
  from tmp t
 where not exists (select 1 from tmp where abs(num - 11) < abs(t.num - 11))

Answer 3

自定义函数func_sum
create or replace function func_sum(str in varchar2)
return number
as
v_num number;
begin
execute immediate 'select '||str||' from dual' into v_num;
return v_num;
end;

Answer 4

首先谢谢LZ的回复，我测试了你的代码，有如下问题:
1. 当我要查询结果最接近30的记录，应该只显示第7条才对，而你代码显示的结果是
   3,5,6,8 30.23
  查询接近的20的记录，也应该只显示第六条才对，你代码显示的结果是
  1,2,3,5,8 19.832. 我想要的是能够显示满足合计值条件的所有记录，像下面这样显示
    T1_01      T1_02
---------- ----------
         3          5
         2        5.1
         8        1       
   .....
   .....  
  
用过程实现也无所谓。

Answer 5

--1、自定义函数
CREATE OR REPLACE FUNCTION eval(express VARCHAR2) RETURN NUMBER IS
  v_result NUMBER;
BEGIN
  EXECUTE IMMEDIATE 'select ' || express || ' from dual'
    INTO v_result;
  RETURN v_result;
EXCEPTION
  WHEN OTHERS THEN
    RETURN 0;
END;--2、语句(注：如果要求其它值，请修改常量11；如果大量值，将会很慢，这本身就是排列组合问题)
with tt  as
   (
   select 1 T1_01,4.5 T1_02 from dual union all
   select 2 T1_01,5.1 T1_02 from dual union all
   select 3 T1_01, 5 T1_02 from dual union all
   select 4 T1_01,7.2 T1_02 from dual union all
   select 5 T1_01,4.23 T1_02 from dual union all
   select 6 T1_01,20 T1_02 from dual union all
   select 7 T1_01,30 T1_02 from dual union all 
   select 8 T1_01,1 T1_02 from dual   
   ),
   aa as 
  (SELECT t1_id, target
            FROM (SELECT row_number() over(ORDER BY abs(target - 11)) rn, t1_id, target
                    FROM (SELECT eval(sys_connect_by_path(t1_02, '+')) target,
                                 sys_connect_by_path(t1_01, ',') || ',' t1_id
                            FROM (SELECT to_char(tt.t1_02) t1_02, t1_01, rownum prn, rownum + 1 chl
                                    FROM tt)
                          CONNECT BY prn >= PRIOR chl))
           WHERE rn = 1)
SELECT t1_01,t1_02
  FROM tt, aa
 WHERE instr(aa.t1_id, ',' || to_char(tt.t1_01) || ',') > 0;

Answer 6

我这里写错了，要把把聚合这步去掉..  with T1  as
   (
   select 1 T1_01,4.5 T1_02 from dual union all
   select 2 T1_01,5.1 T1_02 from dual union all
   select 3 T1_01, 5 T1_02 from dual union all
   select 4 T1_01,7.2 T1_02 from dual union all
   select 5 T1_01,4.23 T1_02 from dual union all
   select 6 T1_01,20 T1_02 from dual union all
   select 7 T1_01,30 T1_02 from dual union all 
   select 8 T1_01,1 T1_02 from dual   
   )
,tmp as(
select substr(sys_connect_by_path(t1_01, ','), 2) id,
       func_sum(substr(sys_connect_by_path(t1_02, '+'), 2)) num
  from t1
connect by prior t1_01 < t1_01
)
select *
  from tmp t
 where not exists (select 1 from tmp where abs(num - 30) < abs(t.num - 30))

Answer 7

这里用connect by 比较巧妙

Answer 8

谢谢大家的热情参与，辛苦了！ 我在ORACLE中测试了你的代码，现在还没发现什么问题，就是速度比较慢，这种组合后并验证估计也没什么好的办法，只能是缩小查询数据的条件。
这段代码，能改写在MSSQL2005中吗? 我试了下，sys_connect_by_path函数在MSSQL中没有，这段代码应该怎么转？