如何写去掉一个最大值，去掉一个最小值计算结果

[fflcat]

例：

create table test
(a int,b int)

insert test
select 1,2
UNION all
select 2,2
union all
select 3,4
union all
select 21,32

按要求，我需要去掉a列的最小值1 和最大值 21 对应记录，之后计算剩下结果的b列值，谢谢，如何做

[SQL77]

如果有两个最小值咋弄

[xuyuchends]

select sum(t1.b) from test as t1
where  exists(select * from test where t1.a<test.a) 
and exists(select * from test where t1.a>test.a) 

[mustudent]

--2005 later
create table #test
(a int,b int)

Delete From #test
insert #test
select 1,2
UNION all
select 2,2
union all
select 3,4
union all
select 21,32
Union All
Select 1,2

Select a.a,a.b From
(Select Row_Number() Over(Order By a) As ROWID,* From #test) a
 WHere ROWID >=2 And ROWID<=(Select Count(1)-1 From #Test)

[michaelgong]

create table test
(a int,b int)

insert test
select 1,2
UNION all
select 2,2
union all
select 3,4
union all
select 21,32
select * from test
select c.* from test c inner join (select MAX(a) as e,MIN(a) as f  from test) d on c.a!=d.e and c.a!=d.f 

a           b
----------- -----------
1           2
2           2
3           4
21          32

(4 行受影响)

a           b
----------- -----------
2           2
3           4

(2 行受影响)

[Tosp2012]

select  sum(b) as b from Test a  where   a.a>( select  min(a) as a  from  Test ) and a.a< ( select  max(a) as a  from  Test)

[michaelgong]

select b from test c inner join (select MAX(a) as e,MIN(a) as f  from test) d on c.a!=d.e and c.a!=d.f

b
-----------
2
4

(2 行受影响)

[twglinda]

用什么数据库的啊.什么好用啊.不知道

[Shawn]

SELECT avgb=AVG(t.b) FROM
(SELECT rowno=ROW_NUMBER() OVER(ORDER BY a, GETDATE()),* FROM test) t
WHERE t.rowno <> 1 AND t.rowno <> (SELECT COUNT(*) FROM test)
/*
avgb
-----------
3
*/

[duoxu1983]

select sum(col)-max(col)-min(col) as total from tb

[gogodiy]

create table t1
(
	a int,
	b int
)
insert into t1
select 1,2 union all
select 2,2 union all
select 3,4 union all
select 21,32
select * from t1

;with aaa as
(select a from t1 
except
select MIN(a) from t1
except
select MAX(a) from t1)
select SUM(b) as b from t1 where a in (select a from aaa)

[cd731107]

select * from test where a not in (select max(a),min(a) from test)