select 姓名,成绩=avg(成绩) from 表 t where exists(select 1 from 表 where t.姓名= 姓名 and t.成绩>成绩 or T.成绩<成绩) group by 姓名
--> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([姓名] varchar(4),[成绩] int) insert [tb] select '张三',100 union all select '张三',90 union all select '张三',90 union all select '张三',89 union all select '李四',99 union all select '李四',80 union all select '王五',95 union all select '王五',100 union all select '王五',80
select avg(成绩) from tb where 成绩 not in (select max(成绩) from tb) and 成绩 not in (select min(成绩) from tb) /* 92 */
select avg(score) from tb where score<>(select max(score) from tb) and score<>(select min(score) from tb)
SELECT A.姓名,AVG(A.成绩) AS 平均值 FROM 表 AS A LEFT JOIN (SELECT 姓名,MIN(成绩) AS 最低分,MAX(成绩) AS 最高分 FROM 表 GROUP BY 姓名) AS B ON A.姓名 = B.姓名 AND (A.成绩 = B.最低分 OR A.成绩 = B.最高分) WHERE B.姓名 IS NULL GROUP BY A.姓名;
select 姓名,avg(成绩)成绩 from tb t where exists(select 1 from tb where t.姓名=姓名 and t.成绩>成绩 or T.成绩<成绩) group by 姓名
select avg(score) from tb where score<>(select max(score) from tb) and score<>(select min(score) from tb)
--> 生成测试数据: @T DECLARE @T TABLE (姓名 VARCHAR(4),成绩 INT) INSERT INTO @T SELECT '张三',100 UNION ALL SELECT '张三',90 UNION ALL SELECT '张三',90 UNION ALL SELECT '张三',89 UNION ALL SELECT '李四',99 UNION ALL SELECT '李四',80 UNION ALL SELECT '王五',95 UNION ALL SELECT '王五',100 UNION ALL SELECT '王五',80--SQL查询如下:SELECT A.姓名,AVG(A.成绩) AS 平均值 FROM @T AS A LEFT JOIN (SELECT 姓名,MIN(成绩) AS 最低分,MAX(成绩) AS 最高分 FROM @T GROUP BY 姓名) AS B ON A.姓名 = B.姓名 AND (A.成绩 = B.最低分 OR A.成绩 = B.最高分) WHERE B.姓名 IS NULL GROUP BY A.姓名;/* 姓名 平均值 ---- ----------- 王五 95 张三 90(2 row(s) affected)*/
--> 生成测试数据: @T DECLARE @T TABLE (姓名 VARCHAR(4),成绩 INT) INSERT INTO @T SELECT '张三',100 UNION ALL SELECT '张三',90 UNION ALL SELECT '张三',90 UNION ALL SELECT '张三',89 UNION ALL SELECT '李四',99 UNION ALL SELECT '李四',80 UNION ALL SELECT '王五',95 UNION ALL SELECT '王五',100 UNION ALL SELECT '王五',80--SQL查询如下:SELECT A.姓名, CASE WHEN COUNT(*) > 2 THEN (SUM(成绩)-MIN(成绩)-MAX(成绩))/(COUNT(*)-2) ELSE AVG(成绩) END AS 平均值 FROM @T AS A GROUP BY A.姓名;/* 姓名 平均值 ---- ----------- 李四 89 王五 95 张三 90(3 row(s) affected) */
select avg(成绩) from tb where 成绩<>(select max(成绩) from tb) and 成绩<>(select min(成绩) from tb)
select 姓名,avg(成绩)成绩 from tb t where exists(select 1 from tb where t.姓名=姓名 and t.成绩>成绩) and exists(select 1 from tb where t.姓名=姓名 and t.成绩<成绩) group by 姓名
if object_id('[tb]') is not null drop table [tb] go create table [tb]([姓名] varchar(4),[成绩] int) insert [tb] select '张三',100 union all select '张三',90 union all select '张三',90 union all select '张三',89 union all select '李四',99 union all select '李四',80 union all select '王五',95 union all select '王五',100 union all select '王五',80
SELECT * FROM TBSELECT [姓名],AVG([成绩]) FROM TB B WHERE EXISTS(SELECT 1 FROM TB WHERE [成绩]<>(SELECT MAX([成绩])FROM TB) AND [成绩]<>(SELECT MIN([成绩])FROM TB) AND [姓名]=B.姓名 ) GROUP BY [姓名]
----------创建表 CREATE TABLE [dbo].[td_user]([name] [nvarchar](20),[score] [int])----------添加测试数据 insert into [td_user] values('张三',100) insert into [td_user] values('张三',90) insert into [td_user] values('张三',60) insert into [td_user] values('张三',89) insert into [td_user] values('李四',99) insert into [td_user] values('李四',80) insert into [td_user] values('王五',96) insert into [td_user] values('王五',100) insert into [td_user] values('王五',80)-------sql查询 select [name],avg(score) as favg from ( select T.*,TT.fmax,TT.fmin from td_user as T left outer join ( select [name],max(score) fmax,min(score) fmin from td_user group by [name]) as TT ON T.[name]=TT.[name]) as TTT where score > fmin and score < fmax group by [name]
declare @t table ( 姓名 nvarchar(100), 成绩 float )insert @t select '张三' ,100 union all select '张三' ,90 union all select '张三' ,90 union all select '张三' ,89 union all select '李四' ,99 union all select '李四' ,80 union all select '王五' ,95 union all select '王五' ,100 union all select '王五' ,80 select 姓名,(sum(成绩)-max(成绩)-min(成绩))/count(成绩) as 成绩 from @t group by 姓名
姓名 and t.成绩>成绩 or T.成绩<成绩)
group by 姓名
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([姓名] varchar(4),[成绩] int)
insert [tb]
select '张三',100 union all
select '张三',90 union all
select '张三',90 union all
select '张三',89 union all
select '李四',99 union all
select '李四',80 union all
select '王五',95 union all
select '王五',100 union all
select '王五',80
select avg(成绩) from tb where 成绩 not in (select max(成绩) from tb) and 成绩 not in (select min(成绩) from tb)
/*
92
*/
where score<>(select max(score) from tb)
and score<>(select min(score) from tb)
FROM 表 AS A
LEFT JOIN (SELECT 姓名,MIN(成绩) AS 最低分,MAX(成绩) AS 最高分
FROM 表 GROUP BY 姓名) AS B
ON A.姓名 = B.姓名 AND (A.成绩 = B.最低分 OR A.成绩 = B.最高分)
WHERE B.姓名 IS NULL
GROUP BY A.姓名;
姓名,avg(成绩)成绩
from
tb t
where
exists(select 1 from tb where t.姓名=姓名 and t.成绩>成绩 or T.成绩<成绩)
group by
姓名
select avg(score) from tb
where score<>(select max(score) from tb)
and score<>(select min(score) from tb)
-- Author : liangCK 小梁
-- Comment: 小梁 爱 兰儿
-- Date : 2009-09-09 10:59:55
-------------------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (姓名 VARCHAR(4),成绩 INT)
INSERT INTO @T
SELECT '张三',100 UNION ALL
SELECT '张三',90 UNION ALL
SELECT '张三',90 UNION ALL
SELECT '张三',89 UNION ALL
SELECT '李四',99 UNION ALL
SELECT '李四',80 UNION ALL
SELECT '王五',95 UNION ALL
SELECT '王五',100 UNION ALL
SELECT '王五',80--SQL查询如下:SELECT A.姓名,AVG(A.成绩) AS 平均值
FROM @T AS A
LEFT JOIN (SELECT 姓名,MIN(成绩) AS 最低分,MAX(成绩) AS 最高分
FROM @T GROUP BY 姓名) AS B
ON A.姓名 = B.姓名 AND (A.成绩 = B.最低分 OR A.成绩 = B.最高分)
WHERE B.姓名 IS NULL
GROUP BY A.姓名;/*
姓名 平均值
---- -----------
王五 95
张三 90(2 row(s) affected)*/
-- Author : liangCK 小梁
-- Comment: 小梁 爱 兰儿
-- Date : 2009-09-09 10:59:55
-------------------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (姓名 VARCHAR(4),成绩 INT)
INSERT INTO @T
SELECT '张三',100 UNION ALL
SELECT '张三',90 UNION ALL
SELECT '张三',90 UNION ALL
SELECT '张三',89 UNION ALL
SELECT '李四',99 UNION ALL
SELECT '李四',80 UNION ALL
SELECT '王五',95 UNION ALL
SELECT '王五',100 UNION ALL
SELECT '王五',80--SQL查询如下:SELECT A.姓名,
CASE WHEN COUNT(*) > 2 THEN (SUM(成绩)-MIN(成绩)-MAX(成绩))/(COUNT(*)-2)
ELSE AVG(成绩) END AS 平均值
FROM @T AS A
GROUP BY A.姓名;/*
姓名 平均值
---- -----------
李四 89
王五 95
张三 90(3 row(s) affected)
*/
where 成绩<>(select max(成绩) from tb) and 成绩<>(select min(成绩) from tb)
select
姓名,avg(成绩)成绩
from
tb t
where
exists(select 1 from tb where t.姓名=姓名 and t.成绩>成绩)
and exists(select 1 from tb where t.姓名=姓名 and t.成绩<成绩)
group by
姓名
go
create table [tb]([姓名] varchar(4),[成绩] int)
insert [tb]
select '张三',100 union all
select '张三',90 union all
select '张三',90 union all
select '张三',89 union all
select '李四',99 union all
select '李四',80 union all
select '王五',95 union all
select '王五',100 union all
select '王五',80
SELECT * FROM TBSELECT [姓名],AVG([成绩])
FROM TB B
WHERE
EXISTS(SELECT 1 FROM TB WHERE [成绩]<>(SELECT MAX([成绩])FROM TB)
AND [成绩]<>(SELECT MIN([成绩])FROM TB) AND [姓名]=B.姓名 )
GROUP BY [姓名]
----------创建表
CREATE TABLE [dbo].[td_user]([name] [nvarchar](20),[score] [int])----------添加测试数据
insert into [td_user] values('张三',100)
insert into [td_user] values('张三',90)
insert into [td_user] values('张三',60)
insert into [td_user] values('张三',89)
insert into [td_user] values('李四',99)
insert into [td_user] values('李四',80)
insert into [td_user] values('王五',96)
insert into [td_user] values('王五',100)
insert into [td_user] values('王五',80)-------sql查询
select [name],avg(score) as favg from (
select T.*,TT.fmax,TT.fmin from td_user as T
left outer join (
select [name],max(score) fmax,min(score) fmin
from td_user group by [name]) as TT ON T.[name]=TT.[name]) as TTT
where score > fmin and score < fmax
group by [name]
姓名 nvarchar(100),
成绩 float
)insert @t select
'张三' ,100 union all select
'张三' ,90 union all select
'张三' ,90 union all select
'张三' ,89 union all select
'李四' ,99 union all select
'李四' ,80 union all select
'王五' ,95 union all select
'王五' ,100 union all select
'王五' ,80
select 姓名,(sum(成绩)-max(成绩)-min(成绩))/count(成绩) as 成绩 from @t group by 姓名