给数据库查询结果添加一个额外的自增编号

布满荆棘的人生 2024-02-17 09:24 6阅读 0赞

## 1、在mysql数据库可以执行的sql ##

SELECT
    	( @i := @i + 1 ) num,
    	M.* 
    FROM
    	user M,
    	( SELECT @i := 0 ) AS ID 
    GROUP BY
    	M.ID 
    ORDER BY
    	M.create_time

**SELECT (@i :=@i + 1)** 是为了生成自增的序列号字段

**SELECT @i := 0** 是为了将i进行初始化每次查询的序列号都会从1开始进行排序生成序列号

在mysql中执行的结果：

![8d35b7471c2946c4a51395e6c85244fa.png][]

## 2、在postgresql数据库可以执行的sql ##

SELECT 
    	ROW_NUMBER() OVER (ORDER BY create_time) AS two_num,
    	id AS two_id,
    	factory_area AS two_factory_area,
    	enter_date AS two_enter_date,
    	classes AS two_classes,
    	teams AS two_teams,
    	enter_car_no AS two_enter_car_no,
    	enter_time AS two_enter_time,
    	stop_time AS two_stop_time,
    	mineral_num AS two_mineral_num,
    	unit AS two_unit,
    	blocked_up AS two_blocked_up,
    	loading AS two_loading,
    	remark AS two_remark
    FROM
    	dispatch_enter_soil_record
    WHERE
    	del_flag = '0'
        and factory_area = 'qt_two'
        and enter_date = '2023-11-17'
    	and classes = '1'
        and teams = '4'
    order by create_time

在postgresql中执行的结果：

![a41c5906de0c4355baba24eb1f311c0e.png][]

#### row\_number() 函数 ####

`row_number()` 函数返回当前行所在的分区内的序号，从 1 开始。

语法

row_number()
    OVER (
      [PARTITION BY partition_column_list]
      [ORDER BY order_column_list]
    )

*`partition_column_list`*参与分区的列的列表。

*`order_column_list`*参与排序的列的列表。

[8d35b7471c2946c4a51395e6c85244fa.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/06/15a159ca6c50412a87c27b214e6d5eda.png
[a41c5906de0c4355baba24eb1f311c0e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/06/d0fc2f40facf4e68aa056161c1fb4aa7.png