一个比较难的sql语句

[losedxyz]

现在有一个表，

cj(姓名,评委一,评委二,评委三,评委四,评委五)

想把评委中打分最高与最低外的三者的平均分求出并按其高低排序，如何写sql？

[jetdw]

明天起来再写了
睡觉去了

[hhhdyj]

创建以下函数用来实现

CREATE FUNCTION AV(@1 FLOAT, @2 FLOAT, @3 FLOAT, @4 FLOAT, @5 FLOAT)
RETURNS FLOAT
AS
BEGIN
DECLARE @R FLOAT
DECLARE @T TABLE(A FLOAT)
INSERT INTO @T
SELECT @1
UNION ALL SELECT @2
UNION ALL SELECT @3
UNION ALL SELECT @4
UNION ALL SELECT @5
SELECT @R = SUM(A) - MIN(A) - MAX(A) FROM @T
RETURN @R / 3
END

SELECT 姓名, dbo.AV(评委一,评委二,评委三,评委四,评委五) AS 成绩
FROM CJ
ORDER BY 成绩

[kisa99]

select 姓名,avg((select 姓名, sum(成绩) from cj group by 姓名)as 成绩 -
(select min(成绩) from cj group by 成绩 group by 姓名) as 成绩1 -
(select max(成绩) from cj group by 成绩 group by 姓名) as 成绩3) as 成绩 from cj group by 姓名 order by 成绩

[gc_ding]

(1)测试数据
create table cj
(姓名 nvarchar(50),
评委一 int,
评委二 int,
评委三 int,
评委四 int,
评委五 int)

insert cj
select '张三',100,99,98,97,96 union all
select '李四',99,98,97,96,95 union all
select '王五',80,81,82,83,84 union all
select '曾六',90,91,92,93,94

（2）查询
select 姓名,avg(评委一+评委二+评委三+评委四+评委五)/5 as 平均分 from cj
where
评委一 not in
(
select max(分数) from
(
select 姓名,max(评委一)as 分数 from cj group by 姓名 union all
select 姓名,max(评委二)as 分数 from cj group by 姓名 union all
select 姓名,max(评委三)as 分数 from cj group by 姓名 union all
select 姓名,max(评委四)as 分数 from cj group by 姓名 union all
select 姓名,max(评委五)as 分数 from cj group by 姓名
-- order by 姓名,分数
) as X
group by 姓名
)
or
评委二 not in
(
select max(分数) from
(
select 姓名,max(评委一)as 分数 from cj group by 姓名 union all
select 姓名,max(评委二)as 分数 from cj group by 姓名 union all
select 姓名,max(评委三)as 分数 from cj group by 姓名 union all
select 姓名,max(评委四)as 分数 from cj group by 姓名 union all
select 姓名,max(评委五)as 分数 from cj group by 姓名
-- order by 姓名,分数
) as X
group by 姓名
)
or
评委三 not in
(
select max(分数) from
(
select 姓名,max(评委一)as 分数 from cj group by 姓名 union all
select 姓名,max(评委二)as 分数 from cj group by 姓名 union all
select 姓名,max(评委三)as 分数 from cj group by 姓名 union all
select 姓名,max(评委四)as 分数 from cj group by 姓名 union all
select 姓名,max(评委五)as 分数 from cj group by 姓名
-- order by 姓名,分数
) as X
group by 姓名
)
or
评委四 not in
(
select max(分数) from
(
select 姓名,max(评委一)as 分数 from cj group by 姓名 union all
select 姓名,max(评委二)as 分数 from cj group by 姓名 union all
select 姓名,max(评委三)as 分数 from cj group by 姓名 union all
select 姓名,max(评委四)as 分数 from cj group by 姓名 union all
select 姓名,max(评委五)as 分数 from cj group by 姓名
-- order by 姓名,分数
) as X
group by 姓名
)
or
评委五 not in
(
select max(分数) from
(
select 姓名,max(评委一)as 分数 from cj group by 姓名 union all
select 姓名,max(评委二)as 分数 from cj group by 姓名 union all
select 姓名,max(评委三)as 分数 from cj group by 姓名 union all
select 姓名,max(评委四)as 分数 from cj group by 姓名 union all
select 姓名,max(评委五)as 分数 from cj group by 姓名
-- order by 姓名,分数
) as X
group by 姓名
)
group by 姓名
order by 平均分 desc

（3）结果
/*
姓名 平均分
张三 98
李四 97
曾六 92
王五 82
*/

[lengxiaowei]

create table cj
(
姓名 varchar(20),
评委一 decimal(12,2),
评委二 decimal(12,2),
评委三 decimal(12,2),
评委四 decimal(12,2),
评委五 decimal(12,2)

)

insert cj select '王二',90,60,85,95,50
union all select '李四',80,50,45,90,60


select 姓名,平均成绩=(sum(评委一)-min(评委一)-max(评委一))/3 into #tmp1 from
(
select 姓名,评委一 from cj
union all select 姓名,评委二 from cj
union all select 姓名,评委三 from cj
union all select 姓名,评委四 from cj
union all select 姓名,评委五 from cj
) a
group by 姓名

select * from #tmp1 order by 平均成绩 desc

[losedxyz]

谢谢各位

[jackeyabc]

收藏了