Home Database Mysql Tutorial mysql数据库 实现类似标记序号的伪列_MySQL

mysql数据库 实现类似标记序号的伪列_MySQL

Jun 01, 2016 pm 01:44 PM
database

bitsCN.com

在查询数据库的时候,我们有时候需要对查询出来的数据加上序列,1,2,3,……n

例如:我们根据表的某个字段排序后,要对这些数据加上序列,这个时候序号常常不是我们建表时设置好的自增的主键id,怎么办呢????

可能我们会用变量来解决,如下

set @rownum=0;

SELECT @rownum:=@rownum+1 AS rownum, table_name.*
FROM  table_name

 

如果在数据库中要用一句SQL语句来实现,这又改怎么办呢????

oracle数据库有专门的rownum来解决伪序列问题,但是mysql却到目前为止还没有内置的函数等来解决此问题

这个问题也困扰了我一段时间,到各种论坛上发帖也没有能回答,最后在无意间找到了这种解决方法

 

SELECT @rownum:=@rownum+1 AS rownum, table_name.*
FROM (SELECT @rownum:=0) r, table_name

把rownum的设置初值放到了from 和需要查询的表的前面

原文来自“老鸟的大心脏”,在这里谢谢他。

希望能帮助更多需要帮助的人……

也希望更多的高手们提出更多的漂亮的方法……

也许有人也会用到存储过程来解决,那也是一个方法,我曾经也用过,但是后来不能用存储过程了,就悲剧了

摘自 ystyaoshengting的专栏

bitsCN.com
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot Article Tags

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

How does Go language implement the addition, deletion, modification and query operations of the database? How does Go language implement the addition, deletion, modification and query operations of the database? Mar 27, 2024 pm 09:39 PM

How does Go language implement the addition, deletion, modification and query operations of the database?

Detailed tutorial on establishing a database connection using MySQLi in PHP Detailed tutorial on establishing a database connection using MySQLi in PHP Jun 04, 2024 pm 01:42 PM

Detailed tutorial on establishing a database connection using MySQLi in PHP

Comparison of similarities and differences between MySQL and PL/SQL Comparison of similarities and differences between MySQL and PL/SQL Mar 16, 2024 am 11:15 AM

Comparison of similarities and differences between MySQL and PL/SQL

How does Hibernate implement polymorphic mapping? How does Hibernate implement polymorphic mapping? Apr 17, 2024 pm 12:09 PM

How does Hibernate implement polymorphic mapping?

iOS 18 adds a new 'Recovered' album function to retrieve lost or damaged photos iOS 18 adds a new 'Recovered' album function to retrieve lost or damaged photos Jul 18, 2024 am 05:48 AM

iOS 18 adds a new 'Recovered' album function to retrieve lost or damaged photos

An in-depth analysis of how HTML reads the database An in-depth analysis of how HTML reads the database Apr 09, 2024 pm 12:36 PM

An in-depth analysis of how HTML reads the database

Analysis of the basic principles of MySQL database management system Analysis of the basic principles of MySQL database management system Mar 25, 2024 pm 12:42 PM

Analysis of the basic principles of MySQL database management system

Tips and practices for handling Chinese garbled characters in databases with PHP Tips and practices for handling Chinese garbled characters in databases with PHP Mar 27, 2024 pm 05:21 PM

Tips and practices for handling Chinese garbled characters in databases with PHP

See all articles