MySQL给查询加序号

末蓝、 2023-10-07 20:55 63阅读 0赞

#### 阅读目录 ####

*  数据表
 *  MySQL给查询加序号
 *   *  解释说明
     *  相关知识点

## 数据表 ##

DROP TABLE IF EXISTS tb_score;
    CREATE TABLE tb_score(
        id INT(11) NOT NULL auto_increment,
        userid VARCHAR(20) NOT NULL COMMENT '用户id',
        subject VARCHAR(20) COMMENT '科目',
        score DOUBLE COMMENT '成绩',
        PRIMARY KEY(id)
    )ENGINE = INNODB DEFAULT CHARSET = utf8;
    
    INSERT INTO tb_score(userid,subject,score) VALUES ('001','语文',90);
    INSERT INTO tb_score(userid,subject,score) VALUES ('001','数学',92);
    INSERT INTO tb_score(userid,subject,score) VALUES ('001','英语',80);
    INSERT INTO tb_score(userid,subject,score) VALUES ('002','语文',88);
    INSERT INTO tb_score(userid,subject,score) VALUES ('002','数学',90);
    INSERT INTO tb_score(userid,subject,score) VALUES ('002','英语',75.5);
    INSERT INTO tb_score(userid,subject,score) VALUES ('003','语文',70);
    INSERT INTO tb_score(userid,subject,score) VALUES ('003','数学',85);
    INSERT INTO tb_score(userid,subject,score) VALUES ('003','英语',90);
    INSERT INTO tb_score(userid,subject,score) VALUES ('003','政治',82);

## MySQL给查询加序号 ##

一种普遍的解决方法是：通过定义用户变量生成序号

举例：查询表中的数据并加上序号，对应的SQL为:

SELECT
    	id,userid,subject,score, (@i :=@i + 1) AS '序号'
    FROM
    	tb_score,
    	(SELECT @i := 0) AS itable;

+----+--------+---------+-------+--------+
    | id | userid | subject | score | 序号   |
    +----+--------+---------+-------+--------+
    |  1 | 001    | 语文    |    90 |      1 |
    |  2 | 001    | 数学    |    92 |      2 |
    |  3 | 001    | 英语    |    80 |      3 |
    |  4 | 002    | 语文    |    88 |      4 |
    |  5 | 002    | 数学    |    90 |      5 |
    |  6 | 002    | 英语    |  75.5 |      6 |
    |  7 | 003    | 语文    |    70 |      7 |
    |  8 | 003    | 数学    |    85 |      8 |
    |  9 | 003    | 英语    |    90 |      9 |
    | 10 | 003    | 政治    |    82 |     10 |
    +----+--------+---------+-------+--------+
    10 rows in set (0.00 sec)

SELECT
    	*, (@i :=@i + 1) AS '序号'
    FROM
    	tb_score;

+----+--------+---------+-------+--------+
    | id | userid | subject | score | 序号   |
    +----+--------+---------+-------+--------+
    |  1 | 001    | 语文    |    90 |     11 |
    |  2 | 001    | 数学    |    92 |     12 |
    |  3 | 001    | 英语    |    80 |     13 |
    |  4 | 002    | 语文    |    88 |     14 |
    |  5 | 002    | 数学    |    90 |     15 |
    |  6 | 002    | 英语    |  75.5 |     16 |
    |  7 | 003    | 语文    |    70 |     17 |
    |  8 | 003    | 数学    |    85 |     18 |
    |  9 | 003    | 英语    |    90 |     19 |
    | 10 | 003    | 政治    |    82 |     20 |
    +----+--------+---------+-------+--------+
    10 rows in set (0.00 sec)

或者

SET @i=0;
    SELECT id,userid,subject,score,@i:=@i+1 AS '序号' 
    FROM tb_score;

### 解释说明 ###

1、`(@i:=@i+1)` 也可以写成 `@i:=@i+1`，加括号是为了视觉上更清晰。

它代表的意思是：变量 `i` 加 `1` 赋值给变量 `i`，在定义好一个变量后每次查询都会给这个变量自增，每次执行查询语句获取结果后就不需要这个变量自增了。

2、`(SELECT @i:=0) AS itable`，定义用户变量 `i`，设置初始值为 `0`，然后将它作为派生表使用，AS 定义了表的别名。

3、`SET @i=0`。定义用户变量 `i`，赋初值为 `0`。

### 相关知识点 ###

1、MySQL定义用户变量的方式：  
`select @变量名` ，上面的SQL语句中，变量的名字是 `i`。

2、用户变量赋值：一种是直接用 `"="` 号，另一种是用 `":="` 号。

`=` 和 `:=` 的区别

使用 set 命令对用户变量进行赋值时，两种方式都可以使用，即：`SET @变量名=xxx` 或 `SET @变量名:=xxx`