Use an SQL statement in the MySQL database to get this record and its previous and next records

Tag:des   io    use    ar    data    sp    on   c    code    

When you make a fuss page, you sometimes need to show the previous and next records below the article. If you simply use the ID value plus 1 and minus 1 methods to do this there will be a flaw. Crossing Please imagine if we delete one of the middle and a few records? It's not coming out, is it? So, thought is very important, um. For example, I have a table called XXX, which has a unique ID field. Then there's n data in it ~ ~ ~ Now, let's take a look at our thinking. We are going to find the previous and next one based on the known ID, that is, the three records adjacent to the database, the middle one is OK. Since the IDs are likely to be discontinuous, let's use the > and < operators (if the limit supports negative values it's not so troublesome ...). ）。 Well, take this record (for illustrative purposes, we assume that the ID of the known record is 123). The SQL statement below takes the Sqlselect*from ' XXX ' WHERE ' id ' of this record =123  that, get it next? We can use the ' id ' >123 as a condition. But in the database ' ID ' more than 123 of the data may be more than one, then use limit bar. Take the next record of the known record: SELECT * from ' XXX ' WHERE ' id ' > 123 limit 1 Of course, you have to write limit 0,1 I have no opinion ~ that is to take the last one? With ' id ' <123 LIMIT 1? The Impossible ~ must be the first one to take. What's the last one to take? (There is a small voice under the table said: with sort ~ ~ ~) to! SELECT * from ' XXX ' WHERE ' id ' <123 ORDER by ' id ' DESC LIMIT 1 See if it's the last one? OK, so we merge the SQL statements, well, this and the next good good merge, the code is as follows select * from ' XXX ' WHERE ' id ' >=123 LIMIT 2  that to add the previous record? Do not forget that we have a union function in MySQL Oh ~ ~ do not know the children's shoes can go to see the manual. In order to prevent the order of the records from being garbled, let's add a sort to each SQL. The summary code comes out: (select*from ' xxx ' where ' id ' <123orderby ' id ' desclimit 1) UNION (select*from ' xxx ' where ' id ' >= 123ORDERBY ' id ' asclimit 2)   by the way, the sort field is not necessarily ' id ', it can be any field in your database table, but the mostGood is not to have to repeat yo. It is important to note that the order by in the two SELECT statements will be in the same field, and one in ascending order is descending, do not make the same OH ~ quickly experiment!!!

Use an SQL statement in the MySQL database to get this record and its previous and next records