有一张表
例 A
B C D
-----------
a 10 1
b 12 2
c 11 1
d 8 1
e 9 2
f 80 1
g 12 2
h 34 2
条件:1.输入的数字 - 字段C 的 绝对值 最小的三个。
我现在要取出 D = 1 和D = 2的放同一个临时表里去。例如:输入10
出来表B
B D
-----------
a 1
c 1
d 1
b 2
g 2
e 2
例 A
B C D
-----------
a 10 1
b 12 2
c 11 1
d 8 1
e 9 2
f 80 1
g 12 2
h 34 2
条件:1.输入的数字 - 字段C 的 绝对值 最小的三个。
我现在要取出 D = 1 和D = 2的放同一个临时表里去。例如:输入10
出来表B
B D
-----------
a 1
c 1
d 1
b 2
g 2
e 2
这个select a.* from (select b,abs(10-c) as c,d from A ) a where ((select count(*) from (select b,abs(10-c) as c,d from A ) b where a.d=b.d and a.c>b.c)<3) order by d---结果
b c d
---------- ----------- -----------
a 0 1
c 1 1
d 2 1
b 2 2
e 1 2
g 2 2
如果你的B 列是唯一的select aa.* from (select b,abs(10-c) as c,d from A ) aa where b in (select top 3 b from (select b,abs(10-c) as c,d from A ) b where b.d=aa.d order by b ) order by d
from #table1 where lu=@lu and cishu = 1 order by num
union all
select top 3 quname, mianji,zongjia,abs(nong-@nong) as num
from #table1 where lu=@lu and cishu = 2
order by num为什么不行?
set @i=10
select b,d from ta t where (select count(1) from ta where abs(@i-c)<abs(@i-t.c))<3如果有并列数值时,05用row_number函数,2000可用临时表或函数
declare @ta table (B nvarchar(2), C int, D int)
insert @ta select 'a', 10, 1
union all select 'b', 12, 2
union all select 'c', 11, 1
union all select 'd', 8, 1
union all select 'e', 9, 2
union all select 'f', 80, 1
union all select 'g', 12, 2
union all select 'h', 34, 2declare @i int
set @i=10
select * from @ta t where (select count(1) from @ta where d=t.d and abs(@i-c)<abs(@i-t.c))<3 order by d(所影响的行数为 8 行)B C D
---- ----------- -----------
c 11 1
d 8 1
a 10 1
b 12 2
e 9 2
g 12 2(所影响的行数为 6 行)
kk19840210(飞天小虫) ( ) 信誉:100 2007-8-15 17:31:10
roy_88(中国风_燃烧你的激情!!!) ( ) 信誉:100 2007-8-15 19:44:01 你们写的对于相减之后值相同的都会出现错误。
我后来写了两个,在后台解决了。还是很感谢大家。