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

💗💗 作者：小5聊
🏆🏆 原则：Write Less Do More!
📋📋 简介：一只喜欢全栈方向的程序员，专注基础和实战分享，欢迎咨询，尽绵薄之力答疑解惑！
🎉🎉 场景：根据学生的C语言成绩，能够查询出不同班级前3名或前10名学生成绩排名

文章目录

1、查询场景

1）场景描述

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

2）预期效果

查询语句试下如下表格效果
以下举例展示一年级一班和一年级二班语文成绩排在前十名的学生排名。
这里模拟的姓名数据，可以通过之前的一篇文章来生成，点击跳转随机生成姓名记录
编号班级排名姓名C语言成绩111软件1班1赵慧100211软件1班2胡歆98311软件1班3郭菲歆95411软件1班4朱苑92511软件1班5张星静90611软件2班1周嫣嫣89711软件2班2刘菲柔88811软件2班3孙嫣星85911软件2班4林珊821011软件2班5林羽791111软件3班1陈巧静891211软件3班2胡慕沐881311软件3班3刘嘉绮851411软件3班4徐素821511软件3班5何星79

2、知识点

1）排名函数

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

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

2）over()对比排序

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')isnull)begincreatetable ClassCourseStuentScoreTable(
        id intidentity(1,1)primarykey,
        className nvarchar(50),
        courseName nvarchar(50),
        studentName nvarchar(50),
        scoreValue int,
        addTime datetime)endelsebeginprint('表已存在')end

4、模拟数据

1）90条记录

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

2）完整代码

-- 班级数量declare@classCountintset@classCount=3-- 班级名称declare@className nvarchar(50)set@className=''-- 课程名称declare@courseName nvarchar(50)set@courseName='C语言'-- 学生名称declare@studentName nvarchar(50)set@studentName=''-- 此处放第一层循环代码-- 学生课程成绩declare@scoreValueintdeclare@studentCountintwhile(@classCount>0)beginprint(@classCount)if(@classCount=3)set@className='11软件3班'elseif(@classCount=2)set@className='11软件2班'elseif(@classCount=1)set@className='11软件1班'-- 此处放第二层循环代码--set@studentCount=30while(@studentCount>0)begin-- ===随机姓名===-- 定义一个姓氏表变量，表添加两个字段，自增编号和名字declare@surnameTabletable(
            id intidentity(1,1)primarykey, 
            name nvarchar(10))-- 定义一个名字表变量，表添加两个字段，自增编号和名字declare@nameTabletable(
            id intidentity(1,1)primarykey, 
            name nvarchar(10))insert@surnameTablevalues('王'),('李'),('张'),('刘'),('陈'),('杨'),('黄'),('赵'),('周'),('吴'),('徐'),('孙'),('马'),('胡'),('朱'),('郭'),('何'),('罗'),('高'),('林')insert@nameTablevalues('芸'),('荷'),('星'),('秋'),('嘉'),('娜'),('珊'),('菲'),('素'),('嫣'),('慧'),('慕'),('歆'),('巧'),('绮'),('羽'),('静'),('柔'),('采'),('沐'),('苑'),('姣'),('芳'),('宁'),('沛'),('玥'),('文'),('如'),('悦'),('若'),('德'),('蕾'),('颜'),('依'),('盼'),('菀'),('秀'),('草'),('莺'),('倩'),('柳'),('娴'),('彨'),('舒'),('雅'),('淑'),('冉'),('云'),('凝'),('棋')-- 生成名字declare@name nvarchar(50)declare@nameLengthintset@nameLength=1+round(rand()*1,0)set@name=(select name from@surnameTablewhere id=round(rand()*20+1,0))while(@nameLength>0)beginset@name+=(select name from@nameTablewhere id=round(rand()*20+1,0))set@nameLength-=1endset@studentName=@name-- ===/随机姓名===set@scoreValue=30+round(rand()*70,0)insertinto ClassCourseStuentScoreTable(className,courseName,studentName,scoreValue,addTime)values(@className,@courseName,@studentName,@scoreValue,getdate())set@studentCount-=1end--set@classCount-=1print(@className)endselect*from ClassCourseStuentScoreTable
--truncate table ClassCourseStuentScoreTable

5、查询各个班级前3名

1）查询语句

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

2）查询效果

6、常见函数回顾

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

1）排名函数

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

2）字符串函数

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

3）日期函数

编号函数名用途1getdate()获取当前时间2dateadd()当前时间加减3datediff()时间差4datename()返回星期几字符串5datepart()获取年月日值，整型

4）数学函数

编号函数名1rand()2abs()3ceiling()4floor()5power()6round()7sign()8sqrt()

5）系统函数

编号函数名1convert()2current_user3datalength()4host_name()5system_user6user_name()

6）聚合函数

编号函数名1sum()2avg()3max()4min()5count()

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