用MSSQL能够一条语句搞定吗？

本来不喜欢人家提问说非要一条语句搞定，不过有时找点东西来动动脑筋也蛮有趣。

在ORACLE版看到一个热火朝天的贴子，如下：

评委给歌手打分，求去掉所有最高分和所有最低分后的平均分。只能用一条SQL语句实现，而且不能有子查询。

原贴：http://www.csdn.net/expert/topic/940/940164.xml?temp=.3584558

在ORACLE版好象有办法了，但是用到的ORACLE的特殊用法，不知道有没有办法用MSSQL做到。

create table score (id int,score int)

insert into score values (1,100)
insert into score values (1,70)
insert into score values (1,80)
insert into score values (1,50)
insert into score values (1,50)
insert into score values (1,100)
insert into score values (1,70)

insert into score values (2,70)
insert into score values (2,70)
insert into score values (2,80)
insert into score values (2,50)
insert into score values (2,50)

反正我还没想出来：）

45 个解决方案

#1

好题,可惜我不能实现

#2

既然按钮JJ没有想出来，那我看我就不用想了。

#3

不是去掉一个最高分,去掉一个最低分的吗?
你的例子要的结果如何.

#4

select id,avg(score) as avgscore from score x
where not exists (
select * from (
select id,max(score) as maxscore from score group by id
) as a
where a.id=x.id
and a.maxscore=x.score
)
and not exists (
select * from (
select id,min(score) as minscore from score group by id
) as b
where b.id=x.id
and b.minscore=x.score
)

#5

少了group by
有小数点问题

select id,avg(convert(numeric(10,2),score)) as avgscore from score x
where not exists (
select * from (
select id,max(score) as maxscore from score group by id
) as a
where a.id=x.id
and a.maxscore=x.score
)
and not exists (
select * from (
select id,min(score) as minscore from score group by id
) as b
where b.id=x.id
and b.minscore=x.score
)
group by id

结果：
1 73.333333
2 70.000000

#6

海兄，好象是去掉所有最高分所有最低分

#7

去掉所有最高分及所有最低分，而且不能用子查询。
我想了想，参考ORACLE的作法，如果这个表有一个unique列的话是可以做的，若没有这一列还没想出来。
我是这样加一个unique列可以做出来。

select identity(int,1,1) as fid ,id,score into #score from score

select t0.id,
(sum(distinct t0.score*100+checksum(t0.fid,t0.id,t0.score))
-sum(distinct checksum(t0.fid,t0.id,t0.score)))
/100
/count(distinct checksum(t0.fid,t0.id,t0.score)) as avgscore
from #score t0,#score t1 ,#score t2
where t0.id=t1.id and t0.score <t1.score and t0.id=t2.id  and t0.score>t2.score
group by t0.id

得到是：
id          avgscore
----------- -----------
1           73
2           70

但是用ORACLE没有UNIQUE也可以做哦。

#8

呵呵，没有看清要求！

#9

select id,(sum(score)-max(score)-min(score))/count(*) from score group by id

#10

select id,(sum(score)-max(score)-min(score))/(count(*)-2) from score group by id

＊＊＊＊＊
打工好辛苦
＊＊＊＊＊
钞票好难赚
＊＊＊＊＊
编程好伤神
＊＊＊＊＊
光阴好易混
＊＊＊＊＊

#11

#12

dgz01(打工者) 的比较常用

#13

id是表示歌手的编号还是评委的编号或者是其他意思？

#14

不能用子查詢..
呃~~thinking~~~~zz

#15

TableA has only one field Score(number)

#16

再申明一下：
是除开所有最高分，及所有最低分，最高分可能不止一个，最低分也是这样。
---- ----

并假设还剩下有至少一个分数（特殊情况就不考虑了）。

#17

id是歌手的编号，这里没有记录评委的编号，要是有评委编号就好办了。

#18

问题变成要从两条完全相同的记录里找出不同来，而且不能用子查询，一条语句内，是过用NEWID,不行，不能转成INT，也好像不对。

#19

select id,sum(tmp) - max(tmp)-min(tmp))/(count(id)-2) from tmp group by id

很简单！！！！

#20

reate table tablea (score numeric(5,2))

insert into tablea values(99)
insert into tablea values(99)
insert into tablea values(96)
insert into tablea values(95)
insert into tablea values(94)
insert into tablea values(92)
insert into tablea values(91)
insert into tablea values(90)
insert into tablea values(90)
insert into tablea values(90)
insert into tablea values(93)
insert into tablea values(92)
insert into tablea values(99)

select (f.allsum-b.maxscore*d.maxcount-c.minscore*e.mincount)/(a.allcount-d.maxcount-e.mincount)
from (select count(*) allcount from tablea) a,
(select max(score) maxscore from tablea) b,
(select min(score) minscore from tablea) c,
(select count(a1.score) maxcount from tablea a1,(select max(score) maxscore from tablea) b1
where a1.score=b1.maxscore) d,
(select count(c1.score) mincount from tablea c1,(select min(score) minscore from tablea) d1
where c1.score=d1.minscore) e,
(select sum(score) allsum from tablea) f

有点复杂，希望兄弟们简化一下

结果：93。285714

考虑歌手ID也是一样的道理

#21

ahui_201(ahui) dgz01(打工者) Hookcjh() :
是除去所有最高最低分，可能有并列的情况。

newly_ignorant(不学无术)：题目要求不能用子查询。

#22

想了好几个小时，没招了，痛苦!

#23

SELECT a.id, AVG(a.Score) as AvgScore
FROM Score a LEFT JOIN
(
             SELECT ID,MAX(Score) t1,MIN(Score) t2
             FROM score
             GROUP BY ID
) b
ON a.ID=b.ID AND (a.Score=b.t1 OR a.score=b.t2)
WHERE b.ID IS NULL
GROUP BY a.ID

真的想不出不用子查詢的方法，難道T-SQL真的不行?
呃~~~~~~~

#24

SELECT (a.Sum1 - a. MAX - a. MIN) / (a.Count1 - 2) AS Average
FROM (SELECT COUNT(*) AS Count1, SUM(NO) AS Sum1, MAX(No) AS MAX, MIN(NO)
              AS MIN
        FROM Table2
        GROUP BY ll) a

结果是：74，63

#25

不能用子查詢啊~~

#26

不能用子查詢换句话说就是：只能有一个select出现.
不知道MSSQL有没有类似oracled函数dbms_rowid.ROWID_ROW_NUMBER(rowid)?
不同的数据具有不同的值?(我不懂MSSQL.)
有就解决了.

#27

不知道这样行不行：
select t1.id, avg(cast(s1.score as real)) from score s1
inner join (select s2.id, max(s2.[score]) as [max] from score s2 group by s2.id) t1 on s1.id=t1.id and s1.score<t1.[max]
inner join (select s3.id, min(s3.[score]) as [min] from score s3 group by s3.id) t2 on s1.id=t2.id and s1.score>t2.[min]
group by t1.id

id
----------- -----------------------------------------------------
1           73.333333333333329
2           70.0

#28

楼上还是用了子查询.

本人斗言:此题无解,期待下版的YUKON来解决吧!

#29

高才们都那去了？

#30

MSSQL没有类似oracled函数dbms_rowid.ROWID_ROW_NUMBER(rowid)的，我是想不出来了：-（

#31

看来MSSQL要输给oracle了.

#32

呵呵,不一定,好象有点头绪了,明天帖出来.

#33

不知用下面的语句行不行：
select s.id,cast(sum(case when s.score<>max(s.score) and  s.score<>min(s.score)) as decimal(6,2)/count(case when s.score<>max(s.score) and s.score<>min(s.score)) as avg_score
   from score s
     where s.id is not null
       group by s.id

#34

不好意思，写错了，应该是：
select s.id,avg(case when s.score<>max(s.score) and  s.score<>min(s.score) then cast(s.score as decimal(6,2) else null end) as avg_score
   from score s
     where s.id is not null
       group by s.id

#35

小了一个括号
而且是错误的：
不能对包含聚合或子查询的表达式执行聚合函数。

#36

select a.id,cast(a.score as real) score from score a inner join score b on a.id=b.id and a.score<b.score inner join score c on a.id=c.id and a.score>c.score
group by a.id,a.score,b.score
order by a.id
compute avg(cast(a.score as real))  by a.id

results:
id          score
----------- -----------
1           100
1           50
1           80
1           70
1           70
1           50
1           100
2           70
2           80
2           80
2           50
2           70

(12 row(s) affected)

id          score
----------- ------------------------
1           70.0
1           70.0
1           80.0

            avg
            =====================================================
            73.333333333333329

id          score
----------- ------------------------
2           70.0

            avg
            =====================================================
            70.0

(6 row(s) affected)

上面还是有问题~~~`
不过提供一个思路。

#37

主要问题在于ORACLE是每条记录更新的，所以可以得到每条记录的ID唯一，而在MSSQL中是批量更新的，所以其唯一ID得自己设置！
我想主要问题还是在这里！

#38

mark

#39

up
继续关注！

#40

(sum - max - min)/count(*)

#41

up!

继续关注！

#42

看来是真没办法了：-（
这几天有点小忙，过几天再来结帐。

#43

别忙结帐，也许过几天有人会想出来。
到时没处贴不就很可惜!

#44

好象是没解了。结账！

#45

惨！痛！

#1