数据表名 T1结构字段和对应值如下:
T1_01 T1_02
1 4.5
2 5.1
3 6
4 7.2
5 4.23
6 20
7 30
.................我想要找到T1_02字段中随机某几条数据之和最接近11的数据条目。
比如: 第一种组合 4.5+5.1=9.6
第二种组合 4.5+6=10.5
第三种组合 4.5+7.2=11.7
第四种组合 4.5+4.23=8.73
第五种组合 5.1+6=11.1
.....
.....
上面只列举中了其中的几种合计组合,其中第五中最接近,即是第2条记录和第3条记录之和。
那么结果就只显示最接近的这组数据记录。
就只显示
T1_01 T1_02
2 5.1
3 6
这两条记录。
完成这个SQL用基本的方法其实不是很困难,之前有写过,但是查询速度太慢了,所以请高人指点迷津,如何写这样的SQL查询,用过程,函数都无所谓,只要能达到速度最快。
ORACLE SQL....
T1_01 T1_02
1 4.5
2 5.1
3 6
4 7.2
5 4.23
6 20
7 30
.................我想要找到T1_02字段中随机某几条数据之和最接近11的数据条目。
比如: 第一种组合 4.5+5.1=9.6
第二种组合 4.5+6=10.5
第三种组合 4.5+7.2=11.7
第四种组合 4.5+4.23=8.73
第五种组合 5.1+6=11.1
.....
.....
上面只列举中了其中的几种合计组合,其中第五中最接近,即是第2条记录和第3条记录之和。
那么结果就只显示最接近的这组数据记录。
就只显示
T1_01 T1_02
2 5.1
3 6
这两条记录。
完成这个SQL用基本的方法其实不是很困难,之前有写过,但是查询速度太慢了,所以请高人指点迷津,如何写这样的SQL查询,用过程,函数都无所谓,只要能达到速度最快。
ORACLE SQL....
SQL>
SQL> with T1 as
2 (
3 select 1 T1_01,4.5 T1_02 from dual union all
4 select 2 T1_01,5.1 T1_02 from dual union all
5 select 3 T1_01, 6 T1_02 from dual union all
6 select 4 T1_01,7.2 T1_02 from dual union all
7 select 5 T1_01,4.23 T1_02 from dual union all
8 select 6 T1_01,20 T1_02 from dual union all
9 select 7 T1_01,30 T1_02 from dual
10 )
11 select a.*
12 from T1 a,
13 (select t4.col1, t4.col3
14 from (select t3.*, row_number() over(order by col5) rn
15 from (select t1.T1_01 col1,
16 t1.T1_02 col2,
17 t2.T1_01 col3,
18 t2.T1_02 col4,
19 abs(11 - (t2.T1_02 + t1.T1_02)) col5
20 from T1 t1, T1 t2) t3
21 where t3.col1 != t3.col3) t4
22 where t4.rn = 1) b
23 where a.T1_01 = b.col1
24 or a.T1_01 = b.col3
25 / T1_01 T1_02
---------- ----------
3 6
2 5.1SQL>
多谢这位仁兄的回复,我经过测试,发现当我要查询结果为20的时候,出现的是第五条和第六条记录之和
4.23 + 20 ,应该是只显示第六条。
代码中abs(11 - (t2.T1_02 + t1.T1_02)) col5 这里, 是不是要做些改进?
我列举的数据只是个比方,在实际数据库中有很多条记录,没有任何规则只要能以最快的速度找到最接近某个数字的所有记录显示出来即可。
(
select 1 T1_01,1 T1_02 from dual union all
select 2 T1_01,2 T1_02 from dual union all
select 3 T1_01, 9 T1_02 from dual union all
select 4 T1_01,12 T1_02 from dual union all
select 5 T1_01,10 T1_02 from dual
)
select * from(
select a.T1_02 , a1.T1_02 T1_02_1 ,dense_rank() over(order by abs(a.T1_02 + a1.T1_02-11) asc) dn,
row_number() over(partition by a.T1_01 order by a.T1_01) rm
from a, a a1
)
where dn=1 and rm=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;