SQL求超过该科平均分的人

表和数据

drop table if exists grade;
CREATE TABLE  grade(
`id` int(4) NOT NULL,
`job` varchar(32) NOT NULL,
`score` int(10) NOT NULL,
PRIMARY KEY (`id`));

INSERT INTO grade VALUES
(1,'C++',11001),
(2,'C++',10000),
(3,'C++',9000),
(4,'Java',12000),
(5,'Java',13000),
(6,'JS',12000),
(7,'JS',11000),
(8,'JS',9999),
(9,'Java',12500);
select * from grade;

解法一:(看到这个题解使我想把这个题记录下来)

-- 窗口函数
select 
	id,job,score 
from  
	(select id,job,score,avg(score)over(partition by job)av from `grade`) tmp
where score>tmp.av
order by id

中间开窗之后的结果

解法二:直接硬刚连接

SELECT 
    id,g1.job,score 
from 
    grade g1
join 
    (select job,avg(score) av from grade group by job) g2
on g1.job=g2.job
where g1.score>g2.av
order by g1.job

中间聚合的结果

原题

原题