这样吗?最接近平均值的数算中间值么? -- ============================================= -- Author: T.O.P -- Create date: 2009/11/25 -- Version: SQL SERVER 2005 -- ============================================= declare @TB table([id] int,[num] numeric(3,1)) insert @TB select 1,0.2 union all select 2,0.1 union all select 3,0.5 union all select 4,11 union all select 5,8 SELECT TOP 1 ID, NUM FROM @TB ORDER BY ABS(NUM-(select AVG(NUM) from @TB)) ASC --测试结果: /* ID NUM ----------- --------------------------------------- 3 0.5(1 row(s) affected)*/
--> 生成测试数据: @tb DECLARE @tb TABLE (id int,num numeric(3,1)) INSERT INTO @tb SELECT 1,0.2 UNION ALL SELECT 2,0.1 UNION ALL SELECT 3,0.5 UNION ALL SELECT 4,11 UNION ALL SELECT 5,8--SQL查询如下:select * from @tb where num not in((select max(num) from @tb),(select min(num) from @tb)) select a.* from @tb as a left join (select min(num) as minnum,max(num) as maxnum from @tb) as b on a.num = b.minnum or a.num=b.maxnum where b.minnum is null
select * from Table where id >(select min(id) from table) And id <(select max(id)from table)
select num from table1 where num <> (select max(num) from table1) and num<> (select min(num) from table1)
早说嘛 -- ============================================= -- Author: T.O.P -- Create date: 2009/11/25 -- Version: SQL SERVER 2005 -- ============================================= declare @TB table([id] int,[num] numeric(3,1)) insert @TB select 1,0.2 union all select 2,0.1 union all select 3,0.5 union all select 4,11 union all select 5,8select * from @TB t where not exists(select 1 from @tb where num<t.num) or not exists(select 1 from @tb where num>t.num) --测试结果: /* id num ----------- --------------------------------------- 2 0.1 4 11.0(2 row(s) affected) */
select * from tb where no not in ( select max(no) from tb union all select min(no) from tb )
-- =============================================
-- Author: T.O.P
-- Create date: 2009/11/25
-- Version: SQL SERVER 2005
-- =============================================
declare @TB table([id] int,[num] numeric(3,1))
insert @TB
select 1,0.2 union all
select 2,0.1 union all
select 3,0.5 union all
select 4,11 union all
select 5,8
SELECT TOP 1 ID, NUM
FROM @TB
ORDER BY ABS(NUM-(select AVG(NUM) from @TB)) ASC
--测试结果:
/*
ID NUM
----------- ---------------------------------------
3 0.5(1 row(s) affected)*/
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-11-25 09:50:40
-------------------------------------
--> 生成测试数据: @tb
DECLARE @tb TABLE (id int,num numeric(3,1))
INSERT INTO @tb
SELECT 1,0.2 UNION ALL
SELECT 2,0.1 UNION ALL
SELECT 3,0.5 UNION ALL
SELECT 4,11 UNION ALL
SELECT 5,8--SQL查询如下:select * from @tb
where num not in((select max(num) from @tb),(select min(num) from @tb))
select a.* from @tb as a
left join (select min(num) as minnum,max(num) as maxnum from @tb) as b
on a.num = b.minnum or a.num=b.maxnum
where b.minnum is null
早说嘛
-- =============================================
-- Author: T.O.P
-- Create date: 2009/11/25
-- Version: SQL SERVER 2005
-- =============================================
declare @TB table([id] int,[num] numeric(3,1))
insert @TB
select 1,0.2 union all
select 2,0.1 union all
select 3,0.5 union all
select 4,11 union all
select 5,8select *
from @TB t
where not exists(select 1 from @tb where num<t.num)
or not exists(select 1 from @tb where num>t.num)
--测试结果:
/*
id num
----------- ---------------------------------------
2 0.1
4 11.0(2 row(s) affected)
*/
from tb
where no not in
(
select max(no) from tb
union all
select min(no) from tb
)