今天同事给了一个小需求,从一个存有不定数量坐标数据的表(map_trace)中每隔20条取一条。最后写了下面这条SQL:
select * from (select @n:=@n+1 as n, a.* from (select * from map_trace order by CREATE_time desc)a,(select @n:=0)b)c where c.n%20<2 and c.n%20!=0;
在此复习下相关知识:
生成一个字段(非表中字段)用以记录排序 (类比为oracle数据库中的rownum)
MySQL中一个表(表名:stuscore),字段有:id(主键)、stuid(学号)、sname(学生姓名)、subject(课程名称)、score(分数)
列出数学成绩排名 (要求显示字段:排名,姓名 , 课程名称, 分数 , 学号)
用于排名的字段,查询时此处将其用 pm表示
select (@i:=@i+1)pm,s.* from stuscore s,(select @i:=0)t where s.subject='数学' order by score desc;
思想:事先定义一个变量i,通过变量递加以及虚拟表的联查达到生成序列号的目的。
这里,在开始是定义一个变量i,让它每增一条结果是➕1,@i:=1;
这里顺带复习下mysql定义用户变量的方式:select @变量名。
对用户变量赋值有两种方式,一种是直接用"="号,另一种是用":="号。其区别在于使用set命令对用户变量进行赋值时,两种方式都可以使用;当使用select语句对用户变量进行赋值时,只能使用":="方式,因为在select语句中,"="号被看作是比较操作符(@i:=@i+1),也可以写成@i:=@i+1,加括号是为了视觉上看这结构更清楚些。在定义好一个变量后每次查询都会给这个变量自增,而我们每次执行查询语句获取结果后就不需要这个变量自增了,所以要把它重置为0,在表名后用逗号分格下使用 (SELECT @i:=0) as i
就可以了,说下这个as i为什么要这样用,是因为派生表必须需要一个别名,这个就是做它的别名,可以任意字符。
select * from table1,table2 不加任何条件时,是把两个表的数据笛卡尔积之后显示,如上sql中的 t 这张表 其实就是 一条 i=0 的数据
mysql存储过程中,定义变量有两种方式:
1、使用set或select直接赋值,变量名以 @开头
赋值符号:使用set 时可以用 “=“或者“:=” 但是使用select赋值时必须使用“:=”赋值
例如: set@id=1;可以在一个会话的任何地方声明,作用域是整个会话,称为会话变量。mysql总变量不用事先声明,在用的时候直接用@变量名 使用即可
【先行记录】
2、以 DECLARE 关键字声明的变量,只能在存储过程中使用,称为存储过程变量,例如:
DECLARE var1 INT DEFAULT 0;
主要用在存储过程中,或者是给存储传参数中
两者的区别是:
在调用存储过程时,以DECLARE声明的变量都会被初始化为 NULL。而会话变量(即@开头的变量)则不会被再初始化,在一个会话内,只须初始化一次,之后在会话内都是对上一次计算的结果,就相当于在是这个会话内的全局变量。在存储过程中,使用动态语句,预处理时,动态内容必须赋给一个会话变量。
例:
set @v_sql= sqltext;
PREPARE stmt FROM @v_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;