闲着的大虾,来玩个sql语句,电视上常见的评分规则:
表(Table) 表属性(id,name,);
表里面数据是评委给各选手的评分信息,name为选手名字,(number类型)为评的分数,;
现在要去掉最高分和最低分(当然你想加点难度就去掉一个最高分和一个最低分),sql语句的输出结果是各选手的平均分列表;语句越精简越好SQL
表(Table) 表属性(id,name,);
表里面数据是评委给各选手的评分信息,name为选手名字,(number类型)为评的分数,;
现在要去掉最高分和最低分(当然你想加点难度就去掉一个最高分和一个最低分),sql语句的输出结果是各选手的平均分列表;语句越精简越好SQL
解决方案 »
- 如果比较Oracle中的表是否一致
- Suse 升级libc后,Oracle 11g不能启动,求解决
- oracle 导入csv文件问题
- 一个复杂的select语句求详细讲解 在线等
- 如何将EXCEL数据导入ORACLE?????
- 请问一下 form builder 里面有想 java 中 break; 的关键字
- ORA-01461: can bind a LONG value only for insert into a LONG column
- 用exp导出问题
- distinct如何在oracle中实现多表查询?
- Oracle中的SELECT语句的一个关于AS的问题
- group by分组查询00979报错问题
- Oracle SQL Developer的SQL工作表不能正常编辑
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY ID ORDER BY MARK) AS RN1,
ROW_NUMBER() OVER(PARTITION BY ID ORDER BY MARK DESC) AS RN2,
ID,
NAME,
MARK
FROM TEST)
WHERE RN1 <> 1
AND RN2 <> 1
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY ID ORDER BY MARK) AS RN1,
ROW_NUMBER() OVER(PARTITION BY ID ORDER BY MARK DESC) AS RN2,
ID,
NAME,
MARK
FROM TEST)
WHERE RN1 <> 1
AND RN2 <> 1
GROUP BY ID, NAME
2.只去掉一个
一共2个sql随便写一个 或都写上
SELECT ID, NAME, AVG(MARK)
FROM (SELECT RANK() OVER(PARTITION BY ID ORDER BY MARK) AS RN1,
RANK() OVER(PARTITION BY ID ORDER BY MARK DESC) AS RN2,
ID,
NAME,
MARK
FROM TEST)
WHERE RN1 <> 1
AND RN2 <> 1
GROUP BY ID, NAME分类:2.只去掉一个
SELECT ID, NAME, AVG(MARK)
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY ID ORDER BY MARK) AS RN1,
ROW_NUMBER() OVER(PARTITION BY ID ORDER BY MARK DESC) AS RN2,
ID,
NAME,
MARK
FROM TEST)
WHERE RN1 <> 1
AND RN2 <> 1
GROUP BY ID, NAME
select name,avg()
from table
where (name,) not in(
select name,max()
from table
group by name
union
select name,min()
from table
group by name
)
group by name2.只去掉一个
select name,(sum()-max()-min())/(count(*)-2)
from table
group by name
having count(*)>2
select a.name,avg(a.) from (select * from aaaa t where exists (select * from aaaa where t.name=name and t. > ) and exists (select * from aaaa where t.name=name and t. < )) a group by a.name order by a.name asc;
select id, name, (sum() - max() - min())/(count(*) - 2)
from table
group by id, name;
select t.name, avg(t.)
from aaaa t
where exists (select 1
from aaaa t2
where t2.name = t.name
and t2. > t. )
and exists (select 1
from aaaa t3
where t3.name = t.name
and t3. < t.)
group by t.name
order by t.name asc;
(select id, name, score,
max(score) over(partition by name) lo,
min(score) over(partition by name)hi
from a_up) group by name
create table t_grade(id int, name varchar2(20),grade int);
-- id 选手ID
-- name 评委名
-- grade 份数
insert into t_grade
select 1, 'aa',90 from dual union
select 1, 'bb',75 from dual union
select 1, 'cc',90 from dual union
select 1, 'dd',85 from dual union
select 1, 'ee',85 from dual union
select 1, 'ff',90 from dual union
select 1, 'gg',75 from dual union
select 2, 'aa',88 from dual union
select 2, 'bb',78 from dual union
select 2, 'cc',90 from dual union
select 2, 'dd',85 from dual union
select 2, 'ee',85 from dual union
select 2, 'ff',90 from dual union
select 2, 'gg',78 from dual;select id, avg(grade) grade
from (select row_number() over(partition by id order by grade) rn1,
count(1) over(partition by id) rn2,
t.*
from t_grade t) t
where rn1 not in (1,rn2)
group by id;-- 双色球机选号码
-- 红球 01 - 33
-- 篮球 01 - 16
SELECT REPLACE(WMSYS.WM_CONCAT(TO_CHAR(RN, '00')), ',', ' ') BELL
FROM (SELECT 'RED' BELL, RN
FROM (SELECT ROWNUM RN
FROM DUAL
CONNECT BY ROWNUM <= 33
ORDER BY DBMS_RANDOM.value)
WHERE ROWNUM <= 6
UNION
SELECT 'BLUE' BELL, TRUNC(DBMS_RANDOM.VALUE(1, 17)) RN
FROM DUAL
ORDER BY BELL DESC, RN) T;
create table t_grade(id int, name varchar2(20),grade int);
-- id 选手ID
-- name 评委名
-- grade 份数
insert into t_grade
select 1, 'aa',90 from dual union
select 1, 'bb',75 from dual union
select 1, 'cc',90 from dual union
select 1, 'dd',85 from dual union
select 1, 'ee',85 from dual union
select 1, 'ff',90 from dual union
select 1, 'gg',75 from dual union
select 2, 'aa',88 from dual union
select 2, 'bb',78 from dual union
select 2, 'cc',90 from dual union
select 2, 'dd',85 from dual union
select 2, 'ee',85 from dual union
select 2, 'ff',90 from dual union
select 2, 'gg',78 from dual;select id, avg(grade) grade
from (select row_number() over(partition by id order by grade) rn1,
count(1) over(partition by id) rn2,
t.*
from t_grade t) t
where rn1 not in (1,rn2)
group by id;贴错代码了
from
(select
id,
name,
,
dense_rank()over(order by desc) rn1,
dense_rank()over(order by asc) rn2
from tb)
where
rn1<>1 and rn2<>12、最高(低)分只删掉其中一个:select id,name,
from
(select
id,
name,
,
row_number()over(order by desc) rn1,
row_number()over(order by asc) rn2
from tb)
where
rn1<>1 and rn2<>1