我只知道sql里面随机取数的是 order by newid() 现在关键是不知道怎末取数相加 与100 比较,然后系统列出 sum后的和最接近100 的所有行;
假设A表的列为colselect m1.col , m2.col , m3.col from A m1,A m2,A m3 , ( select mincol = min(abs(t1.col + t2.col + t3.col - 100)) from A t1,A t2,A t3 where t1.col < t2.col and t2.col < t3.col ) t where abs(m1.col + m2.col + m3.col - 100) = t.mincol
create table A(col int) insert into A values(1) insert into A values(2) insert into A values(3) insert into A values(80) insert into A values(200) insert into A values(3000) goselect m1.col , m2.col , m3.col from A m1,A m2,A m3 , ( select mincol = min(abs(t1.col + t2.col + t3.col - 100)) from A t1,A t2,A t3 where t1.col < t2.col and t2.col < t3.col ) t where m1.col < m2.col and m2.col < m3.col and abs(m1.col + m2.col + m3.col - 100) = t.mincol drop table A/* col col col ----------- ----------- ----------- 2 3 80(所影响的行数为 1 行) */
(
select mincol = min(abs(t1.col + t2.col + t3.col - 100))
from A t1,A t2,A t3
where t1.col < t2.col and t2.col < t3.col
) t
where abs(m1.col + m2.col + m3.col - 100) = t.mincol
insert into A values(1)
insert into A values(2)
insert into A values(3)
insert into A values(80)
insert into A values(200)
insert into A values(3000)
goselect m1.col , m2.col , m3.col from A m1,A m2,A m3 ,
(
select mincol = min(abs(t1.col + t2.col + t3.col - 100))
from A t1,A t2,A t3
where t1.col < t2.col and t2.col < t3.col
) t
where m1.col < m2.col and m2.col < m3.col and abs(m1.col + m2.col + m3.col - 100) = t.mincol drop table A/*
col col col
----------- ----------- -----------
2 3 80(所影响的行数为 1 行)
*/