首页 > 数据库 >SQL 变量写法、排序问题 <12>

SQL 变量写法、排序问题 <12>

时间:2024-08-14 21:26:29浏览次数:17  
标签:sco 12 last -- sum clazz SQL 写法 id

一、定义变量排序

目的1:合并学生表和分数表,将每个班分别排名

目的2:遇到相同分数,考虑还是不考虑相同分数排名

学生表(1000条)和分数表(6000条)分别如下


1、定义变量、简答排序

首先先看一段简单代码:

set @i:=0; -- 定义一个变量i,初始化值为1

SELECT 
	*  -- 查询所有的学生表信息
	,@i:=@i+1  -- 建立排名列,类似于循环 
FROM student

其结果为:除了最后一行的排序字段为新增的,其余的都是student表中的字段

解析代码:

        首先使用set定义一个变量 i ,此时要使用@:=来定义,并且定义完要在最后加上分号表示这条语句单独执行

        使用select语句查询student表时,创建一个新列用来存放排名,排名从1开始,每行加1,所以可以使用外界定义的变量来建立类似于循环的用法

2、不考虑同分排序

先看代码:

-- 定义两个变量
set @i:=0; 
set @last_clazz:='';

SELECT 
	*  -- 输出tt1表的所有字段
	,@last_clazz  -- 定义一个空字段名,此时一整列数据都为空,配合下面赋值语句的结果为将一整列下移一行
	,IF(clazz=@last_clazz,@i:=@i+1,@i:=1) as rank  -- 复制了一整列数据后,判断相同行数据是否一致,如果一致则说明还是一个班级里,排名继续增长,如果不一样,则将i值重新定义为1,从头再开始增长
	,@last_clazz:=clazz	 -- 将clazz班级名全部复制一遍定义成一个新列
FROM
-- 下列查询得到的表为将两个表合并成一个表
	(SELECT 
		t1.*
		,t2.sum_sco
	FROM student t1
	JOIN (SELECT id,sum(score)as sum_sco FROM score GROUP BY id) t2
	ON t1.id = t2.id
	ORDER BY clazz DESC,sum_sco DESC) tt1 -- 先运行括号内的语句,此时已经先对每个班的班级和成绩排好序了,然后在对这整个表进行排名操作

再看结果:

此时可以看出每个班分别的排名情况。

解析代码:

        首先,通过 set @i:=0 设置变量 @i 的初始值为 0。这个变量会在后面的查询中被用来计算排名。
        接着,通过 set @last_clazz:='' 设置变量 @last_clazz 的初始值为。这个变量用于保存上一个班级的值,以便与当前班级进行比较。然后,开始执行查询语句。查询的主体部分是一个子查询 SELECT t1.*, t2.sum_sco FROM student t1 JOIN (SELECT id, sum(score) as sum_sco FROM score GROUP BY id) t2 ON t1.id = t2.id ORDER BY clazz DESC, sum_sco DESC。

        这个子查询首先从 student t1 表中选择所有字段,并与子查询 SELECT id,sum(score) as sum_sco FROM score GROUP BY id 进行连接。子查询的目的是计算每个学生的总分数 sum_sco。连接条件是 t1.id = t2.id,即 student 表的 id 字段与 score 表的 id 字段相等。
然后,查询结果按照 clazz 和 sum_sco 的降序进行排序

        @last_clazz:用于保存上一个班级的值,以便与当前班级进行比较。
        IF(clazz = @last_clazz, @i := @i + 1, @i := 1) as rank:根据当前班级是否与上一个班级相同,来更新排名。如果相同,则将 @i 的值加 1,表示同一班级中的下一个学生。如果不同,则将 @i 的值重置为 1,表示进入了新的班级。
        @last_clazz := clazz:将当前班级的值保存为 @last_clazz,以便在下一行计算排名时使用。

3、考虑同分排序

同样在上述代码的基础下再次对其进行操作:


set @i:=0; -- 记录正常顺序
set @last_clazz:=''; -- 记录是否同班级
set @last_sco:=0;  -- 记录是否同分
SELECT 
	* -- 此时可以打印输出想要的字段,然后重新使用select对这个表进行处理,取出想要的数据
	,@last_sco
	,@last_clazz
	,IF(clazz=@last_clazz,IF(sum_sco=@last_sco,@i,@i:=@i+1),@i:=1) as rank
-- 在对每个班排好名的情况下,再额外对其进行判断,如果分数相同,当前排名不动,反之继续增加排名
	,@last_clazz:=clazz
	,@last_sco:=sum_sco
FROM
	(SELECT  -- 合并两表,取出需要的字段
		t1.*
		,t2.sum_sco
	FROM student t1
	JOIN (SELECT id,sum(score)as sum_sco FROM score GROUP BY id) t2
	ON t1.id = t2.id
	ORDER BY clazz DESC,sum_sco DESC) tt1

运行结果如下所示,即同分排名一致,下列继续顺序排名

如果想要跳过同分这个排名,可以额外再定义一个变量,用来存放分数排名,如果分数一致,相同分数的排名同学一致,后面跳过当前顺序的排名,例如下图排名rank

        

简单修改代码为下列情况:

标签:sco,12,last,--,sum,clazz,SQL,写法,id
From: https://blog.csdn.net/qq_64603703/article/details/141186170

相关文章

  • MySQL-2:数据库基础知识(50%-100%)
    目录前言一、SQL语言基础1.SQL语言简介2.SQL分类3.SELECT语句的使用4.INSERT语句的使用5.UPDATE语句的使用6.DELETE语句的使用二、基本查询1.WHERE子句的使用2.ORDERBY子句的使用3.GROUPBY和HAVING子句使用4.LIMIT子句的使用总结前言前一半MySQL-1:数据库......
  • 8.12 ~ 8.18
    8.12正式复活。然后一晚上就啥都没干。事实证明在家学并不影响效率,因为都很低8.13没有模拟赛,讲了李超线段树和可持久化,感觉还行;然后一天算下来就打了几个板子还没记住;彻底废了,还不如模拟赛呢。8.14上午打模拟赛,然后:不是怎么又背上了......
  • MySQL数据库专栏(三)数据库服务维护操作
    1、界面维护,打开服务窗口找到MySQL服务,右键单击可对服务进行启动、停止、重启等操作。选择属性,还可以设置启动类型为自动、手动、禁用。2、指令维护卸载服务:scdelete [服务名称]例如:scdeleteMySQL启动服务:netstart[服务名称]例如:netstartMySQL停止服务:netsto......
  • 12个科研网站,总有你需要的!
    12个科研网站,总有你需要的!https://mp.weixin.qq.com/s?__biz=MzU1OTkyMTE4Mg==&mid=2247486517&idx=1&sn=0465b8a71c4f3e3e7cfa57cfdb6c1a01&chksm=fc0ead44cb792452654340be67d7304570635c17132a357551e54e311b6ded83e21f32ce88a3&mpshare=1&scene=23&sr......
  • 云计算课程设计(Prometheus+grafana+Flume+ganglia+mysql+jdk)
    一、准备环境prometheus下载地址:https://github.com/prometheus/prometheus/releases/download/v2.52.0-rc.1/prometheus-2.52.0-rc.1.windows-amd64.zipgrafana下载地址:https://dl.grafana.com/enterprise/release/grafana-enterprise-10.4.2.windows-amd64.zip......
  • 【PostgreSQL教程】PostgreSQL 高级篇之约束
    博主介绍:✌全网粉丝20W+,CSDN博客专家、Java领域优质创作者,掘金/华为云/阿里云/InfoQ等平台优质作者、专注于Java技术领域✌技术范围:SpringBoot、SpringCloud、Vue、SSM、HTML、Nodejs、Python、MySQL、PostgreSQL、大数据、物联网、机器学习等设计与开发。感兴趣的可以先......
  • ARC125E Snack
    小清新网络流优化题首先不难想到一个trivial的网络流模型,即建立源点\(S\)和汇点\(T\)对于每个食物\(i\),连\(S\toi\),容量为\(A_i\)的边;对于每个人\(j\),连\(j\toT\),容量为\(C_j\)的边;同时所有食物向每个人\(j\)连容量为\(B_j\)的边直接跑Dinic复杂度显然爆......
  • 第一阶段复习 --Python、Linux、Shell、MySQL
    文章目录第一阶段复习总结python1.python是面向对象的解释型动态数据语言的高级程序设计语言2.变量3.基础数据类型4.数据类型之间的转换5.str类型字符串方法序列6.运算符7.选择循环结构8.数据容器9.函数参数10.类11.正则12.文件读写13.异常处理tryexc......
  • sqli-labs靶场通关攻略
    一,进入sqli-labs-master靶场第一关,加入参数id.列如:127.0.0.1二.判断SQL注入漏洞--引起报错找到闭合与注释加单引号判断闭合,发现为字符型注入,三.使用orderby判断字段数有几列:3列回显正常,4列出现报错,说明只有3列四.使用unionselect确定回显点五.看到2和3的回显查......
  • openGauss怎么工作SQL函数接口读取逻辑解码结果?
    功能描述在openGauss中如果实现数据复制呢?可以通过数据迁移工具定期向目标数据库进行数据库的同步,说的定期,这就意味着这种方式不能满足数据实时复制的需求。在openGauss中为我们提供了逻辑解码功能,工作原理就是反解xlog,从而生成逻辑日志,在目标数据库中通过对逻辑进行解析......