【Sql Server】查询实战，实现不同班级的排行查询并且批量模拟数据进行查询测试

1、查询场景

1场景描述

能够根据不同的班级查询每个班级C语言成绩前10名同学序号从1到10升序显示。
如果是做学校相关项目那么接触到这类需求的应该会很常见

2预期效果

查询语句试下如下表格效果
以下举例展示一年级一班和一年级二班语文成绩排在前十名的学生排名。
这里模拟的姓名数据可以通过之前的一篇文章来生成点击跳转随机生成姓名记录

编号	班级	排名	姓名	C语言成绩
1	11软件1班	1	赵慧	100
2	11软件1班	2	胡歆	98
3	11软件1班	3	郭菲歆	95
4	11软件1班	4	朱苑	92
5	11软件1班	5	张星静	90
6	11软件2班	1	周嫣嫣	89
7	11软件2班	2	刘菲柔	88
8	11软件2班	3	孙嫣星	85
9	11软件2班	4	林珊	82
10	11软件2班	5	林羽	79
11	11软件3班	1	陈巧静	89
12	11软件3班	2	胡慕沐	88
13	11软件3班	3	刘嘉绮	85
14	11软件3班	4	徐素	82
15	11软件3班	5	何星	79

2、知识点

1排名函数

row_number()和over()
从下图可知必须两者结合起来使用。用于对排序产生新的序号有了新的序号就可以重新进行排序

它也是2005开始有的新函数也常用于高效的分页

2over()对比排序

over()里头的分组以及排序的执行会在where 、group by、 order by之后执行
比如根据添加时间降序或者升序根据实际业务需求来定

3判断表是否存在

object_id(N’表名称’)
如果存在表那么会返回这个表的id值id字段属于sysObjects系统对象表

3、创建成绩表

1简单描述

此处通过创建单张表进行模拟数据【班级课程学生成绩表】

2字段设计

自增编号、班级名称、学生名称、课程名称、成绩、添加时间

3表命名小技巧

可以从整体到细节从大模块和具体功能比如ClassCourseStudentScoreTable组成单词=Class+Course+Student+Score+Table
在实际工作中可能每个地方都会有一定的命名标准都是大同小异目的是为了快速识别表的描述和作用否则就增加了运维成本

if(object_id(N'ClassCourseStuentScoreTable') is null) begin
    create table ClassCourseStuentScoreTable(
        id int identity(1,1) primary key,
        className nvarchar(50),
        courseName nvarchar(50),
        studentName nvarchar(50),
        scoreValue int,
        addTime datetime
    )
end
else begin
    print('表已存在')
end

4、模拟数据

190条记录

模拟3个班级每个班级30名学生的C语言成绩总共90条记录

2完整代码

-- 班级数量
declare @classCount int
set @classCount=3
 
-- 班级名称
declare @className nvarchar(50)
set @className=''
 
-- 课程名称
declare @courseName nvarchar(50)
set @courseName='C语言'
 
-- 学生名称
declare @studentName nvarchar(50)
set @studentName=''

-- 此处放第一层循环代码
-- 学生课程成绩
declare @scoreValue int
declare @studentCount int

while(@classCount>0) begin
    print(@classCount)
    
    if(@classCount=3)
        set @className='11软件3班'
    else if(@classCount=2)
        set @className='11软件2班'
    else if(@classCount=1)
        set @className='11软件1班'
    

    -- 此处放第二层循环代码
    --
    set @studentCount=30
    while(@studentCount>0) begin
        -- ===随机姓名===
        -- 定义一个姓氏表变量表添加两个字段自增编号和名字
        declare @surnameTable table(
            id int identity(1,1) primary key, 
            name nvarchar(10)
        )
        
        -- 定义一个名字表变量表添加两个字段自增编号和名字
        declare @nameTable table(
            id int identity(1,1) primary key, 
            name nvarchar(10)
        )
        
        insert @surnameTable values
        ('王'),('李'),('张'),('刘'),('陈'),('杨'),('黄'),('赵'),('周'),('吴'),
        ('徐'),('孙'),('马'),('胡'),('朱'),('郭'),('何'),('罗'),('高'),('林')
        
        insert @nameTable values
        ('芸'),('荷'),('星'),('秋'),('嘉'),('娜'),('珊'),('菲'),('素'),('嫣'),
        ('慧'),('慕'),('歆'),('巧'),('绮'),('羽'),('静'),('柔'),('采'),('沐'),
        ('苑'),('姣'),('芳'),('宁'),('沛'),('玥'),('文'),('如'),('悦'),('若'),
        ('德'),('蕾'),('颜'),('依'),('盼'),('菀'),('秀'),('草'),('莺'),('倩'),
        ('柳'),('娴'),('彨'),('舒'),('雅'),('淑'),('冉'),('云'),('凝'),('棋')
        
        
        -- 生成名字
        declare @name nvarchar(50)
        declare @nameLength int
        set @nameLength=1+round(rand()*1,0)
        set @name=(select name from @surnameTable where id=round(rand()*20+1,0))
        
        while(@nameLength>0) begin
            set @name+=(select name from @nameTable where id=round(rand()*20+1,0))
            set @nameLength-=1
        end
        
        set @studentName=@name
        -- ===/随机姓名===
        
        set @scoreValue=30+round(rand()*70,0)
        
        insert into ClassCourseStuentScoreTable(className,courseName,studentName,scoreValue,addTime)
        values(@className,@courseName,@studentName,@scoreValue,getdate())
        
        set @studentCount-=1
    end
    --
    
    set @classCount-=1
 
    print(@className)
end

select * from ClassCourseStuentScoreTable
--truncate table ClassCourseStuentScoreTable

5、查询各个班级前3名

1查询语句

-- 查询各个班级的前三名
select * from(
    select 
    className 班级,
    courseName 课程,
    studentName 学生,
    scoreValue 成绩,
    row_number() over(partition by className order by scoreValue desc) 排名
    from ClassCourseStuentScoreTable
) a
where a.排名<=3

2查询效果

6、常见函数回顾

Sql Server常用函数分为排名函数、字符串函数、数学函数、系统函数、聚合函数等

1排名函数

编号	函数名	用途
1	row_number()	按顺序对所有行进行编号
2	rank()	为关系提供相同的数值
3	over()	提供数据的升降序

2字符串函数

编号	函数名	用途
1	charindex()	字符索引
2	len()	统计字符串的长度
3	upper()	将字母全部变为大写
4	Ltrim()	移除左边空格
5	Rtrim()	移除右边空格
6	Left()	获取左边指定长度字符串
7	Right()	获取右边指定长度字符串
8	stuff()	删除并替换
9	replace()	查询并替换

3日期函数

4数学函数

5系统函数

6聚合函数

🎉🎉 总结不积跬步无以至千里编程进步的方式之一就是不停的练习