MySQL Display line number sort, same table data sort up and down compare

• Demand

1. Need to query whether the novel has broken more, the novel chapter information table has the release time: Pub_time, if the release time interval more than three days even if the break more

• Ideas

1. Query the novel chapter information table, the chapter information according to the publication time, plus line number. Generate Table1 and table2 information

2. Left JOIN Association query, table1 line number is n and table2 line number is n+1 the data publishing time comparison, if there is more than three days, the description is broken more

• Preparatory work

Chapter Table:

CREATE TABLE ' t_chapter ' (  ' id ' varchar (255) NOT NULL COMMENT ' primary key ',  ' auto_code ' varchar (255) NOT null COMMENT ' number ' ,  ' production_number ' varchar (one) NOT null COMMENT ' work number ',  ' pub_time ' datetime DEFAULT NULL COMMENT ' release time ',  PRIMARY KEY (' id ')) engine=innodb DEFAULT Charset=utf8

• Begin
  

1. Sort the chapter table according to the published time in ascending order 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 (specified works)        ORDER by            T.pub_time ASC

Query results are sorted by publication time

• Correlation Query

 select COUNT (1) from (select T.auto_ Code, T.id, T.production_number, T.pub_time, (@rowNum: =@r                Ownum + 1) as RowNo from T_chapter T, (SELECT (@rowNum: = 0)) b WHERE T.production_number = 979 ORDER by t.pub_time ASC) table1 INNER JOI            N (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 timestampdi FF (day, Table2.pub_time, table1.pub_time) > 3; 

If the query count>0 the work number 979 is broken, more conditions can be determined according to their own business

Description

I didn't know what it was at first, and then I searched the MySQL line number to sort the display using a custom variable (MySQL feature)

Reference blog:

SQL Server Query line number
MYSQL rownum Implementation
MYSQL Custom variable usage (recommended)

• Demand

1. Need to query whether the novel has broken more, the novel chapter information table has the release time: Pub_time, if the release time interval more than three days even if the break more

• Ideas

1. Query the novel chapter information table, the chapter information according to the publication time, plus line number. Generate Table1 and table2 information

2. Left JOIN Association query, table1 line number is n and table2 line number is n+1 the data publishing time comparison, if there is more than three days, the description is broken more

• Preparatory work

Chapter Table:

CREATE TABLE ' t_chapter ' (  ' id ' varchar (255) NOT NULL COMMENT ' primary key ',  ' auto_code ' varchar (255) NOT null COMMENT ' number ' ,  ' production_number ' varchar (one) NOT null COMMENT ' work number ',  ' pub_time ' datetime DEFAULT NULL COMMENT ' release time ',  PRIMARY KEY (' id ')) engine=innodb DEFAULT Charset=utf8

• Begin
  

1. Sort the chapter table according to the published time in ascending order and display the line number

        SELECT            T.auto_code,            t.id,            t.production_number,            t.pub_time,            (@rowNum: = @rowNum + 1) as rowno< C13/>from            T_chapter T,            (SELECT (@rowNum: = 0)) b        WHERE            t.production_number = 1414 (specified works)        ORDER by            T.pub_time ASC

Query results are sorted by publication time

• Correlation Query

 select COUNT (1) from (select T.auto_ Code, T.id, T.production_number, T.pub_time, (@rowNum: =@r                Ownum + 1) as RowNo from T_chapter T, (SELECT (@rowNum: = 0)) b WHERE T.production_number = 979 ORDER by t.pub_time ASC) table1 INNER JOI            N (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 timestampdi FF (day, Table2.pub_time, table1.pub_time) > 3; 

If the query count>0 the work number 979 is broken, more conditions can be determined according to their own business

Description

I didn't know what it was at first, and then I searched the MySQL line number to sort the display using a custom variable (MySQL feature)

Related articles:

Comparison of bubble sort, insert sort, selection sorting algorithm in C language

Insert sort Sort Algorithm Learn-insert sort

Related videos:

Database MySQL Video tutorial