declare @班级表table
(
班级编号int,
班级名称nvarchar(50)
)
insert into @班级表(班级编号,班级名称)
select 1,'三年级一班 3-1'
union all
select 2,'三年级二班 3-2'
union all
select 3,'三年级三班 3-3'
declare @成绩表table
(
学生编号int,
班级编号int,
学生姓名nvarchar(50),
语文numeric(18,1),
数学numeric(18,1)
)
insert into @成绩表(学生编号,班级编号,学生姓名,语文,数学)
select 1,1,'3-1 A同学',85,91
union all
select 2,1,'3-1 B同学',93,96
union all
select 3,1,'3-1 C同学',87,81
union all
select 4,2,'3-2 A同学',87,82
union all
select 5,2,'3-2 B同学',79,84
union all
select 6,3,'3-3 A同学',93,95
union all
select 7,3,'3-3 B同学',93,95
union all
select 8,3,'3-3 C同学',93,95
--取每个班语文第一相同成绩并列
select*from (
select RANK()OVER(PARTITION BY b.班级编号ORDER BY b.语文DESC) Px,b.*,a.班级名称
from @班级表as a inner join @成绩表as b on a.班级编号=b.班级编号
)as tbc where Px=1
order by班级编号
select*from @成绩表as a inner join @班级表as b on a.班级编号=b.班级编号order by b.班级编号