34,838
社区成员




with cte as
(
select *,row_number() over (partition by 姓名 order by 分数 desc)as rn1 from tb1
)
select 姓名, sum(分数) as 分数,row_number() over ( order by sum(分数) desc)as 名次 from cte
where rn1<=3 and 姓名 in (select 姓名 from tb1 group by 姓名 having count (*)>=3) group by 姓名
if object_id('[tb1]') is not null drop table [tb1]
go
create table tb1 (rn int, 分数 numeric(4,2) , 姓名 varchar(10))
insert into tb1
select 1 ,8.0,'王五'union all
select 2 ,7.0,'王五'union all
select 3 ,4.0,'王五'union all
select 1 ,10.0 ,'张三'union all
select 2 ,9.0,'张三'union all
select 3 ,9.0,'张三'union all
select 4 ,8.0,'张三'union all
select 1 ,9.0,'李四'
go
with cte as
(
select *,row_number() over (partition by 姓名 order by getdate())as rn1 from tb1
)
select 姓名, sum(分数) as 分数,row_number() over ( order by getdate())as 名次 from cte where rn1<=3 and 姓名 in (select 姓名 from tb1 group by 姓名 having count (*)>=3) group by 姓名
--姓名 分数 名次
--王五 19.00 1
--张三 28.00 2
if object_id('[tb1]') is not null drop table [tb1]
go
create table tb1 (rn int, 分数 numeric(4,2) , 姓名 varchar(10))
insert into tb1
select 1 ,8.0,'王五'union all
select 2 ,7.0,'王五'union all
select 3 ,4.0,'王五'union all
select 1 ,10.0 ,'张三'union all
select 2 ,9.0,'张三'union all
select 3 ,9.0,'张三'union all
select 4 ,8.0,'张三'union all
select 1 ,9.0,'李四'
go
select 姓名,max(分数) from tb1 where 姓名 in (select 姓名 from tb1 group by 姓名 having count (*)>=3) group by 姓名
--姓名 (无列名)
--王五 8.00
--张三 10.00