是这个意思么
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,'李四'
goselect 姓名,max(分数) from tb1 where 姓名 in (select 姓名 from tb1 group by 姓名 having count (*)>=3) group by 姓名 --姓名 (无列名)
--王五 8.00
--张三 10.00
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,'李四'
goselect 姓名,max(分数) from tb1 where 姓名 in (select 姓名 from tb1 group by 姓名 having count (*)>=3) group by 姓名 --姓名 (无列名)
--王五 8.00
--张三 10.00
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,'李四'
gowith 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
(
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 姓名