MySQL笔记（九）查询结果添加序列号

这是我在学习Mysql之路上做的笔记，今天将它粘出来。这一篇主要是Mysql给查询结果添加序列号。有错误的欢迎大家指出。。。

-- mysql查询结果添加序列号

-- 第一种方法：
SELECT
    (@i :=@i + 1) AS i,
    table_name.*
FROM
    table_name,
    (SELECT @i := 0) AS it;

-- 第二种方法：
SET @rownum = 0;
SELECT
    @rownum :=@rownum + 1 AS rownum,
    t.username
FROM
    auth_user t
LIMIT 1,5;

-- 单表

-- 从0开始
SELECT
   (@i := @i + 1) AS id
FROM
   sys_region n1,
   (SELECT @i := - 1) AS it
LIMIT 1000

-- 从1开始
SELECT
    (@i := @i + 1) AS id
FROM
    sys_region n1,
    (SELECT @i := 0) AS it
LIMIT 1000

-- 多表

-- 从0开始
SELECT
    (@i := @i + 1) AS id
FROM
    sys_region n1
CROSS JOIN sys_region AS n10
CROSS JOIN sys_region AS n100,
 (SELECT @i := - 1) AS it
LIMIT 1000

-- 从1开始
SELECT
    (@i := @i + 1) AS id
FROM
    sys_region n1
CROSS JOIN sys_region AS n10
CROSS JOIN sys_region AS n100,
 (SELECT @i := 0) AS it
LIMIT 1000

-- 实例：mysql：rank名次排名 （并列与不并列）

-- MySQL中不存在类似于SQL Server或Orcal中的rank()函数来得到排名。
-- 排名,（相同分数的相同名次，并列排名）

--创建表score

CREATE TABLE score (
s_id  varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '学生编号' ,
c_id  varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '课程编号' ,
s_score  int(3) NULL DEFAULT NULL COMMENT '分数' ,
PRIMARY KEY (`s_id`, `c_id`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
COMMENT='成绩表'
ROW_FORMAT=COMPACT
;

表score数据

 

SELECT
    b.s_id,    b.c_id,
    -- 顺序一直在变大
    @i:=@i+1 AS 相同分数的不同名次,
  -- 只有在前后二次分数不同时才会使用顺序号
    @k:=(CASE WHEN @s=b.s_score THEN @k ELSE @i END) AS 相同分数的相同名次,
    @s:=b.s_score AS 成绩
FROM
(SELECT *FROM score  WHERE s_id='03' ORDER BY s_score DESC)b,
(SELECT @i:=0,@k:=0,@s:=0)c

结果：

派生表 :是将子查询做为一个表来处理，这个由子查询得出的新表就是我们说的“派生表”。

SQL声明变量：

一、sql server中变量要先申明后赋值：

局部变量用一个@标识，全局变量用两个@（常用的全局变量一般都是已经定义好的）；

声明局部变量语法：declare @变量名 数据类型；例如：declare @num int；

赋值：有两种方法式（@num为变量名，value为值）

set @num=value;   或   select @num=value;

如果想获取查询语句中的一个字段值可以用select给变量赋值,如下：

select @num=字段名 from 表名 where ……

二、mysql中变量不用事前声明，在用的时候直接用“@变量名”使用就可以了。

第一种用法：set @num=1; 或set @num:=1; //这里要使用变量来保存数据，直接使用@num变量

第二种用法：select @num:=1; 或 select @num:=字段名 from 表名 where ……

注意上面两种赋值符号，使用set时可以用“=”或“：=”，但是使用select时必须用“：=赋值”因为在select语句中，"="号被看作是比较操作符。

1、局部变量

局部变量一般用在sql语句块中，比如存储过程的begin/end。其作用域仅限于该语句块，在该语句块执行完毕后，局部变量就消失了。
局部变量一般用declare来声明，可以使用default来说明默认值。

2、用户变量。

用户变量的作用域要比局部变量要广。用户变量可以作用于当前整个连接，但是当当前连接断开后，其所定义的用户变量都会消失。
用户变量使用如下： SELECT @变量名