难题征解，看看谁的SQL厉害？

Question

假设打分表为employee，打分为totalscore,歌手为checkedname
select a.checkedname,(a.sum1 - a.max1 - a.min1)/(count1-2) from 
(select checkedname,sum(totalscore) sum1,count(*) count1,checkedname,max(totalscore) max1,min(totalscore) min1 from employee group by checkedname having count(*) > 2) a

Answer 1

select (sum(分数)-min(分数)-max(分数))/(count(*)-2)
from table
where 歌手＝'歌手名'
/

Answer 2

考，有更优解
select (sum(totalscore)-max(totalscore)-min(totalscore))/(count(*)-2),checkedname from employee group by checkedname having count(*) > 2

Answer 3

好象不对create table employee
(
totalscore   number,
checkedname  varchar2(10)
);insert into employee values (100,'a');
insert into employee values  (20,'a');
insert into employee values  (10,'a');
insert into employee values  (100,'a');
commit;select a.checkedname,(a.sum1 - a.max1 - a.min1)/(count1-2) from 
(select checkedname,sum(totalscore) sum1,count(*) count1,
max(totalscore) max1,min(totalscore) min1 from  
employee group by checkedname having count(*) > 2) aa                                         60

Answer 4

也不对15:25:04 jlanzpa817>select (sum(totalscore)-max(totalscore)-min(totalscore))/(count(*)-2),checkednam
e from employee group by checkedname having count(*) > 2
15:25:05   2  ;(SUM(TOTALSCORE)-MAX(TOTALSCORE)-MIN(TOTALSCORE))/(COUNT(*)-2) CHECKEDNAM
-------------------------------------------------------------- ----------
                                                            60 a

Answer 5

都对了吧
jlandzpa,平均分就是60吧

Answer 6

hehe 
jlandzpa真仔细啊

Answer 7

xzou(亡狼补齿) ，不要考虑0/0的情况:）
 ATCG(ATCG) ：题就这么出的。
以上解答都不对，只是去掉了一个最高分和一个最低分。
各位继续！

Answer 8

to Leehunter(理性猎人) :
有些不明白，你所指的不能有子查询是指WHERE后的SELECT ，还是FROM 后的SELECT ，还是字段列表中的SELECT ,还是都包括?

Answer 9

SELECT SUM(TOTALSCORE)/COUNT(*) FROM (SELECT * FROM EMPLOYEE WHERE TOTALSCORE <>(SELECT MAX(TOTALSCORE) FROM EMPLOYEE) AND TOTALSCORE <>(SELECT  MIN(TOTALSCORE) FROM EMPLOYEE));
SQL> select * from employee;TOTALSCORE CHECKEDNAM
---------- ----------
       100 a
        20 a
        10 a
       100 a
       100 a
        10 a
        20 a
        30 a已选择8行。
SQL> SELECT SUM(TOTALSCORE)/COUNT(*) FROM (SELECT * FROM EMPLOYEE WHERE TOTALSCORE <>(SELECT MAX(TOT
ALSCORE) FROM EMPLOYEE) AND TOTALSCORE <>(SELECT  MIN(TOTALSCORE) FROM EMPLOYEE));SUM(TOTALSCORE)/COUNT(*)
------------------------
               23.333333这样正确了吧

Answer 10

CHENGXB(大山) ：都不能有子查询。
bzszp(SongZip) ：呵呵，你的解答已经有子查询了，CUT掉！

Answer 11

题确定了
而且都不能有select子句，那够难

Answer 12

--尽管需求有点变态，我还是想到了办法19:55:24 jlanzpa817>insert into employee values (100,'a');已创建 1 行。已用时间:  00: 00: 00.20
19:55:24 jlanzpa817>insert into employee values  (20,'a');已创建 1 行。已用时间:  00: 00: 00.10
19:55:24 jlanzpa817>insert into employee values  (10,'a');已创建 1 行。已用时间:  00: 00: 00.10
19:55:24 jlanzpa817>insert into employee values  (100,'a');已创建 1 行。已用时间:  00: 00: 00.40
19:55:24 jlanzpa817>commit;提交完成。已用时间:  00: 00: 00.50
19:55:43 jlanzpa817>select avg(totalscore)
19:56:19   2  from 
19:56:19   3  (select checkedname,totalscore from employee) a,
19:56:19   4  (select max(totalscore) score from employee
19:56:19   5   union 
19:56:19   6   select min(totalscore) score  from employee) b
19:56:19   7  where a.totalscore = b.score(+) and b.score is null;AVG(TOTALSCORE)
---------------
             20已用时间:  00: 00: 00.20
19:56:19 jlanzpa817>

Answer 13

jlandzpa(ORA-00600) :还是用到子查询了，不爽

Answer 14

CHENGXB(大山) (  ) 信誉：100  2002-08-14 17:00:00  得分:0  
 
 
  to Leehunter(理性猎人) :
有些不明白，你所指的不能有子查询是指WHERE后的SELECT ，还是FROM 后的SELECT ，还是字段列表中的SELECT ,还是都包括?
 
Leehunter(理性猎人) (  ) 信誉：100  2002-08-14 17:17:00  得分:0  
 
 
  CHENGXB(大山) ：都不能有子查询。
bzszp(SongZip) ：呵呵，你的解答已经有子查询了，CUT掉！
 
 
这两句一写,估计难倒99%以上的人了

Answer 15

据说这问题是ORACLE大学的校长给他的弟子出的，他的弟子又问了其他人，据说是有解的，但是...还没出现！
至少，我是想破脑袋了！
今晚还是别睡了吧，睡也睡不舒服。

Answer 16

评委给歌手打分，求去掉所有最高分和所有最低分后的平均分。只能用一条SQL语句实现，而且不能有子查询。假设打分表为employee，打分为totalscore,歌手为checkednameselect a.checkedname,sum(a.totalscore)/count(a.totalscore)
from employee a,employee b,employee c
where a.checkedname = b.checkedname and 
a.checkedname = c.checkedname and 
a.totalscore > b.totalscore and 
a.totalscore < b.totalscore 
group by a.checkedname,a.totalscore 这里没有SQL的执行环境，不知道是不是有错。

Answer 17

TO wclarity(水澈) :
你这个SQL 不会有任何结果，因为
select *
from employee a,employee b,employee c
where a.checkedname = b.checkedname and
a.checkedname = c.checkedname and
a.totalscore > b.totalscore and
a.totalscore < b.totalscore
的结果为空，关联的条件自相矛盾！

Answer 18

To:  Leehunter (理性猎人)       能把题目原原本本的COPY出来吗？
还有表结构、有几张表，表之间的关系，
以及提供一部分数据。我想如果客观上
有解，就应该能做出来。
     晚上有空帮你想想，千万别想破
脑袋啊！，脑袋破了，什么都没了。比
我这个快失业的人还要惨了。

Answer 19

写错了。select a.checkedname,avg(a.totalscore)
from employee a,employee b,employee c
where a.checkedname = b.checkedname and 
a.checkedname = c.checkedname and 
a.totalscore > b.totalscore and 
a.totalscore < c.totalscore 
group by a.checkedname

Answer 20

测试了一下，不对，不过这个思路很好.09:09:18 jlanzpa817>select a.checkedname,avg(a.totalscore)
09:11:25   2  from employee a,employee b,employee c
09:11:25   3  where a.checkedname = b.checkedname and 
09:11:25   4   a.checkedname = c.checkedname and 
09:11:25   5   a.totalscore > b.totalscore and 
09:11:25   6   a.totalscore < c.totalscore 
09:11:25   7  group by a.checkedname
09:11:25   8  ;CHECKEDNAM AVG(A.TOTALSCORE)
---------- -----------------
a                         20已用时间:  00: 00: 00.20

Answer 21

sorry，又看错了，方法是对的.

Answer 22

ATCG(ATCG) ：题目就这样了，信息已经足够了吧？简单的说，如：
create table tab_score (score  number);
wclarity(水澈) :算了一下，不对

Answer 23

我的方法，如果最低分总和仍是最低，最高分总和仍是最高的话，结果是正确的。
select (sum(score*count(*))-max(score*count(*))-min(score*count(*)))/(sum(count(*))-max(score*count(*))/max(score)-min(score*count(*))/min(score))
from tab_score group by score
开始以为正确了，后来才发现是特例。

Answer 24

最近太忙，没时间多想
不过根据大家的一点想法提示，写出来一个很菜的结果，大家看看可以优化不
select avg(distinct(a.totalscore)),a.checkedname from employee a,employee b,employee c
where a.checkedname = b. checkedname and a.totalscore<b.totalscore and
a.checkedname = c.checkedname and a.totalscore>c.totalscore group by a.checkedname having count(*) > 2

Answer 25

xzou(亡狼补齿) ：呵呵，应该鼓励！
如果中间没有重复值就正确了。

Answer 26

如果能加一个条件，就是打分totalscore低于某个数字，比如 100，我可以给出一个很复杂的解，
select avg(distinct(dbms_rowid.ROWID_ROW_NUMBER(a.rowid)*100+a.totalscore))-avg(distinct(dbms_rowid.ROWID_ROW_NUMBER(a.rowid)))*100 avg1,
a.checkedname from employee a,employee b,employee c
where a.checkedname = b.checkedname and a.totalscore<b.totalscore and
a.checkedname = c.checkedname and a.totalscore>c.totalscore group by a.checkedname having count(*) > 2

Answer 27

如果更准确地说就是0<=totalscore<=100
select avg(distinct(dbms_rowid.ROWID_ROW_NUMBER(a.rowid)*100+a.totalscore))-avg(distinct(dbms_rowid.ROWID_ROW_NUMBER(a.rowid)))*100 avg1,
a.checkedname from employee a,employee b,employee c
where a.checkedname = b.checkedname and a.totalscore<b.totalscore and
a.checkedname = c.checkedname and a.totalscore>c.totalscore and a.totalscore between 0 and 100 group by a.checkedname having count(*) > 2

Answer 28

xzou(亡狼补齿) ：佩服！真有你的！
有没有更简便的？

Answer 29

改进一下细节
select avg(distinct(dbms_rowid.ROWID_ROW_NUMBER(a.rowid)*100+a.totalscore))-avg(distinct(dbms_rowid.ROWID_ROW_NUMBER(a.rowid))*100) avg1,
a.checkedname from employee a,employee b,employee c
where a.checkedname = b.checkedname and a.totalscore<b.totalscore and
a.checkedname = c.checkedname and a.totalscore>c.totalscore and a.totalscore between 0 and 100 group by a.checkedname having count(*) > 2

Answer 30

17:44:58 jlanzpa817>select * from  employee;TOTALSCORE CHECKEDNAM
---------- ----------
        20 a
        30 a
        10 a
       100 a
       100 a
       100 b
        20 b
        10 b
       100 b已选择9行。已用时间:  00: 00: 00.20
17:45:17 jlanzpa817>select avg(distinct(dbms_rowid.ROWID_ROW_NUMBER(a.rowid)*100+a.totalscore))
17:45:25   2  -avg(distinct(dbms_rowid.ROWID_ROW_NUMBER(a.rowid)))*100 avg1,a.checkedname 
17:45:25   3  from employee a,employee b,employee c
17:45:25   4  where a.checkedname = b.checkedname and a.totalscore<b.totalscore and
17:45:25   5  a.checkedname = c.checkedname and a.totalscore>c.totalscore and 
17:45:25   6  a.totalscore between 0 and 100 group by a.checkedname having count(*) > 2;      AVG1 CHECKEDNAM
---------- ----------
        25 a已用时间:  00: 00: 00.20b的没有出来？

Answer 31

仍旧惯性思维，画蛇添足了，改了一下
select avg(distinct(dbms_rowid.ROWID_ROW_NUMBER(a.rowid)*100+a.totalscore))-avg(distinct(dbms_rowid.ROWID_ROW_NUMBER(a.rowid))*100) avg1,
a.checkedname from employee a,employee b,employee c
where a.checkedname = b.checkedname and a.totalscore<b.totalscore and
a.checkedname = c.checkedname and a.totalscore>c.totalscore and a.totalscore between 0 and 100 group by a.checkedname

Answer 32

佩服，没想到这样的难题这么快就解决了，CSDN正是高手济济呀！
小弟又学了一招，^_^
to  xzou(亡狼补齿) :恭喜你，亡狼补齿同学，你可以从ORACLE大学毕业了！！！              ORACLE大学校长

Answer 33

SELECT AVG(score) FROM TABLE_NAME WHERE score NOT IN (MAX(score), MIN(score));