开窗函数中可以使用子查询吗

题目：用学生的总成绩并进行排名
疑问点：我想用row_number写出排名，但是系统报错（Error Code: 1242. Subquery returns more than 1 row）求大神指点。
我写的查询如下：
select
s_id,
row_number()over(order by (select sum(s_score) from score group by s_id )desc) as ranking
from score

--成绩表
CREATE TABLE Score(
s_id VARCHAR(20),
c_id VARCHAR(20),
s_score INT(3),
PRIMARY KEY(s_id,c_id)
);

--成绩表测试数据
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);

开窗函数可以用子查询，但和=子查询一样，不能返回多值，你这个查询可以调整下

declare @score TABLE(
s_id VARCHAR(20),
c_id VARCHAR(20),
s_score INT
)

insert into @score values('01' , '01' , 80);
insert into @score values('01' , '02' , 90);
insert into @score values('01' , '03' , 99);
insert into @score values('02' , '01' , 70);
insert into @score values('02' , '02' , 60);
insert into @score values('02' , '03' , 80);
insert into @score values('03' , '01' , 80);
insert into @score values('03' , '02' , 80);
insert into @score values('03' , '03' , 80);
insert into @score values('04' , '01' , 50);
insert into @score values('04' , '02' , 30);
insert into @score values('04' , '03' , 20);
insert into @score values('05' , '01' , 76);
insert into @score values('05' , '02' , 87);
insert into @score values('06' , '01' , 31);
insert into @score values('06' , '03' , 34);
insert into @score values('07' , '02' , 89);
insert into @score values('07' , '03' , 98);


select
    a.s_id
    ,dense_rank() over(order by total desc) as ranking
from @score a
left join (
    select s_id,sum(s_score) as total from @score group by s_id
) b on a.s_id=b.s_id

因为你是想根据sum的值排序，所以推测，你应该是想使用 dense_rank这个方式，如果不是，可以自行改回 row_number

如果一定要用你这个，可以修改成这样，加个条件，让他返回单一值即可

select
s_id,
row_number()over(order by (select sum(s_score) from score where s_id=a.s_id group by s_id )desc) as ranking
from score a