Shulou(Shulou.com)05/31 Report--

Editor to share with you the MYSQL display line number sorting, with the table data sorting up and down to compare the example analysis, I believe that most people do not understand, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Demand

You need to check whether there is a break in the novel. There is a release time in the chapter information table of the novel: pub_time. If the release interval is more than three days, it will be changed.

Train of thought

Query the chapter information table of the novel, sort the chapter information according to the release time, and add the line number. Generate table1 and table2 information the same

Left join associated query. The table1 line number n is compared with the data release time of table2 line number nail1. If it exists for more than three days, it will be broken.

Preparatory work

Chapter table:

CREATE TABLE `t _ chapter` (`id` varchar (255) NOT NULL COMMENT 'key', `production_ code `varchar (255) NOT NULL COMMENT 'number', `production_ number`varchar (11) NOT NULL COMMENT 'work number', `pub_ time`datetime DEFAULT NULL COMMENT 'release time', PRIMARY KEY (`id`) ENGINE=InnoDB DEFAULT CHARSET=utf8

Start

Sort the chapter table in ascending order according to the release time and display the line number

SELECT t.auto_code, t.id, t.production_number, t.pub_time, (@ rowNum: = @ rowNum + 1) AS rowNo FROM t_chapter t (SELECT (@ rowNum: = 0)) b WHERE t.production_number = 1414 (designated work) ORDER BY t.pub_time ASC

The query results have been sorted by release time

Association query

SELECT count (1) FROM (SELECT t.auto_code, t.id, t.production_number, t.pub_time, (@ rowNum: = @ rowNum + 1) AS rowNo FROM t_chapter t (SELECT (@ rowNum: = 0)) b WHERE t.production_number = 979 ORDER BY t.pub_time ASC) table1 INNER JOIN (SELECT t.auto_code, t.id, t.production_number, t.pub_time (@ a: = @ a + 1) AS rowNo FROM t_chapter t, (SELECT (@ a: = 0) b WHERE t.production_number = 979 ORDER BY t.pub_time ASC) table2 ON table1.rowNo + 1 = table2.rowNo WHERE timestampdiff (DAY, table2.pub_time, table1.pub_time) > 3

If you query count > 0, there is a break in the work number 979. More conditions can be determined according to your own business.

Description:

I used @ here, and at first I didn't know what it was, and then I searched the mysql line number and found that I used a custom variable (mysql feature) to sort the display.

Refer to the blog:

SQL Server query line number

MYSQL rownum implementation

Use of MYSQL custom variables (recommended)

Demand

You need to check whether there is a break in the novel. There is a release time in the chapter information table of the novel: pub_time. If the release interval is more than three days, it will be changed.

Train of thought

Query the chapter information table of the novel, sort the chapter information according to the release time, and add the line number. Generate table1 and table2 information the same

Left join associated query. The table1 line number n is compared with the data release time of table2 line number nail1. If it exists for more than three days, it will be broken.

Preparatory work

Chapter table:

CREATE TABLE `t _ chapter` (`id` varchar (255) NOT NULL COMMENT 'key', `production_ code `varchar (255) NOT NULL COMMENT 'number', `production_ number`varchar (11) NOT NULL COMMENT 'work number', `pub_ time`datetime DEFAULT NULL COMMENT 'release time', PRIMARY KEY (`id`) ENGINE=InnoDB DEFAULT CHARSET=utf8

Start

Sort the chapter table in ascending order according to the release time and display the line number

SELECT t.auto_code, t.id, t.production_number, t.pub_time, (@ rowNum: = @ rowNum + 1) AS rowNo FROM t_chapter t (SELECT (@ rowNum: = 0)) b WHERE t.production_number = 1414 (designated work) ORDER BY t.pub_time ASC

The query results have been sorted by release time

Association query

SELECT count (1) FROM (SELECT t.auto_code, t.id, t.production_number, t.pub_time, (@ rowNum: = @ rowNum + 1) AS rowNo FROM t_chapter t (SELECT (@ rowNum: = 0)) b WHERE t.production_number = 979 ORDER BY t.pub_time ASC) table1 INNER JOIN (SELECT t.auto_code, t.id, t.production_number, t.pub_time (@ a: = @ a + 1) AS rowNo FROM t_chapter t, (SELECT (@ a: = 0) b WHERE t.production_number = 979 ORDER BY t.pub_time ASC) table2 ON table1.rowNo + 1 = table2.rowNo WHERE timestampdiff (DAY, table2.pub_time, table1.pub_time) > 3

If you query count > 0, there is a break in the work number 979. More conditions can be determined according to your own business.

Description:

I used @ here, and at first I didn't know what it was, and then I searched the mysql line number and found that I used a custom variable (mysql feature) to sort the display.

The above is all the contents of the article "MYSQL shows the sorting of line numbers and compares it with the sorting of data in a table". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.