MYSQL displays row number sorting and compares data sorting from the same table up and down.

• Requirements

1. You need to check whether the novel has been updated. The novel chapter information table has the release time: pub_time, If the release time interval exceeds three days, the update will be discontinued

Thoughts

1. ##Query the novel chapter information table and add the chapter information Sort by release time, plus line number. Generate table1 and table2 with the same information

2. left join related query, table1 row number is n and table2 row number is n 1. Compare the data release time, if there is more than three days, it means the update is interrupted.

Preparation

Chapter List:

CREATE TABLE `t_chapter` (  `id` varchar(255) NOT NULL COMMENT '主键',  `auto_code` varchar(255) NOT NULL COMMENT '编号',  `production_number` varchar(11) NOT NULL COMMENT '作品编号',  `pub_time` datetime DEFAULT NULL COMMENT '发布时间',  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

• Start

  
  

1. Sort the chapter table in ascending order according to release time and display the row 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(指定作品)        ORDER BY
            t.pub_time ASC

Query results Already sorted by release time

• Associated 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 the query count>0, the work number is 979's works are subject to interruption. More conditions can be determined according to your own business.

Description:

@ is used here. I didn’t know what it was at first. Something, then I searched for the mysql row number and found that I used custom variables (mysql features) to sort and display

Reference blog:

SQL Server query row number

MYSQL rownum implementation
MYSQL custom variable usage (recommended)

• Requirements

1. Need to check whether the novel has been updated and the novel chapter information There is a publishing time in the table: pub_time. If the publishing time interval exceeds three days, the update will be interrupted.

Ideas

1. Query The novel chapter information table sorts the chapter information according to the release time and adds the line number. Generate table1 and table2 with the same information

2. left join related query, table1 row number is n and table2 row number is n 1. Compare the data release time, if there is more than three days, it means the update is interrupted.

Preparation

Chapter List:

CREATE TABLE `t_chapter` (  `id` varchar(255) NOT NULL COMMENT '主键',  `auto_code` varchar(255) NOT NULL COMMENT '编号',  `production_number` varchar(11) NOT NULL COMMENT '作品编号',  `pub_time` datetime DEFAULT NULL COMMENT '发布时间',  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

• Start

  
  

1. Sort the chapter table in ascending order according to the release time and display the row 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(指定作品)        ORDER BY
            t.pub_time ASC

The query results have been sorted according to the release time

• Associated 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 the query count>0, the work with work number 979 has a broken update , more conditions can be determined according to your own business

Explanation:

@ is used here. At first I didn’t know what it was, and then I searched for mysql The row number is found and displayed using custom variables (mysql features) for sorting

Related articles:

Bubble sort, insertion sort, selection in c language Sorting algorithm comparison

Insertion sort Sorting algorithm learning-insertion sort

Related videos:

Database mysql video tutorial

The above is the detailed content of MYSQL displays row number sorting and compares data sorting from the same table up and down.. For more information, please follow other related articles on the PHP Chinese website!