select name,class, avg(score) from table group by class,name
select name,class,avg(score) over(partition by class) from table
SQL> select * from test;STUDENT_ID NAME SUBJECT_NAME MARK ---------- -------------------- ------------ ----- 1 学生1 外语 88 1 学生1 物理 96 3 学生3 外语 84 3 学生3 物理 89SQL> SQL> select student_id,name,subject_name,,avg() 2 over(partition by subject_name order by subject_name) 3 as avg_ from test;STUDENT_ID NAME SUBJECT_NAME MARK AVG_MARK ---------- -------------------- ------------ ----- ---------- 1 学生1 外语 88 86 3 学生3 外语 84 86 1 学生1 物理 96 92.5 3 学生3 物理 89 92.5
但是你要取name字段值啊,你对class字段group,当然不能取name,除非你对name和class字段group 你要这样: select class,avg(score) from table group by class 还是这样?select name,class, avg(score) from table group by class,name
不知道你要什么,你说1个class只要1条,但是你1个class对应的name 却有多个,要把1个class对应的所有name和avg求出来就用这个 select name,class,score,avg(score) over(partition by class) from table 如果要随便取class中其中1个name,就用这个 select name,class,avg from( select name,class,score,avg(score) over(partition by class) avg,row_number(partition by class order by score) rn from table ) where rn = 1
是不是这样: SELECT T1.name,T1.class ,T2.SCOREFROM TABLE_1 T1, (SELECT class , AVG(score) SCORE FROM TABLE_1WHERE 1=1 ) T2WHERE 1=1 AND T1.class =T2.class GROUP BY class
给你写一个select class,avg(score),max(name) from table group by class这个是随便取一个NAME的写法
---------- -------------------- ------------ -----
1 学生1 外语 88
1 学生1 物理 96
3 学生3 外语 84
3 学生3 物理 89SQL>
SQL> select student_id,name,subject_name,,avg()
2 over(partition by subject_name order by subject_name)
3 as avg_ from test;STUDENT_ID NAME SUBJECT_NAME MARK AVG_MARK
---------- -------------------- ------------ ----- ----------
1 学生1 外语 88 86
3 学生3 外语 84 86
1 学生1 物理 96 92.5
3 学生3 物理 89 92.5
你要这样:
select class,avg(score) from table group by class
还是这样?select name,class, avg(score) from table group by class,name
select name,class,score,avg(score) over(partition by class) from table
如果要随便取class中其中1个name,就用这个
select name,class,avg from(
select name,class,score,avg(score) over(partition by class) avg,row_number(partition by class order by score) rn from table
)
where rn = 1
SELECT T1.name,T1.class ,T2.SCOREFROM
TABLE_1 T1,
(SELECT class , AVG(score) SCORE
FROM TABLE_1WHERE 1=1 ) T2WHERE 1=1
AND T1.class =T2.class GROUP BY class