例: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列值,谢谢,如何做
(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列值,谢谢,如何做
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)
--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,2Select 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)
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 行受影响)
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)
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.fb
-----------
2
4(2 行受影响)
(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
*/
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)