Classic sql-Get the previous and next articles in the current article

When we do the information site, there will certainly be a need, that is, in the information content page below the need to give the previous and next information link. The last time I was a colleague and friend and roommate met such a demand, at first we all put the problem to the complex, first take the information set to meet the conditions, and then find the current information in the collection of the index, through the index to find his previous information and the next piece of information, This is really the way to achieve this, but once the amount of information is too large, it is likely that this code causes the entire content page to be open for half a day, so you have to go a few ways.

In fact, the problem is very simple, then after our supervisor gave us a little bit of guidance, suddenly feel tall, ah, I did not think of it? It's still too young! The principle of implementation is to go to the database through the ID of the current information to find out the collation of the previous and next information, is not super simple? Look directly at the code!

The current Information content page has an ID of 62 and a professional category ID of 20.

Select Top 1 -1  as [Mark],[ID],[Title]  from [Zixun_info] where [Subjectid]= -  and [ID]< +Union Select Top 1  1  as [Mark],[ID],[Title]  from [Zixun_info] where [Subjectid]= -  and [ID]> +

All right, let's see how it works.

　　

Then check out the top 8 lines.

Select Top 8 *  from [zixun_info] where [subjectid] =  -

Query Result:

　　

Well, it doesn't seem right! The current information content page ID is 62, his previous article should be the ID equals 61 of that piece of information! Why the first one? By default, SQL is sorted by ID in ascending order, so the first article with ID less than 62 is definitely 55, and it has to be sorted, and the last one should be sorted by ID descending, and the next one will be sorted by ID in ascending order.

SQL with the Sort method added:

Select Top 1 -1  as [Sort],[ID],[Title]  from(Select Top 1 [ID],[Title]  from [Zixun_info] where [Subjectid]= -  and [ID]< + Order  by [ID] desc) asTab_up--the previous article is listed in descending order by ID.UnionSelect Top 1  1  as [Sort],[ID],[Title]  from [Zixun_info] where [Subjectid]= -  and [ID]> +  --the next article is sorted by ID in ascending order.

Execution result diagram:

　　

Well, after some twists and turns, we end up in the final, and here you can replace [Mark] with a field that your content page won't use, and I'll use [Sort] instead, so I don't have to change the business model.

Summary: Do not complicate the simple problem, to learn to simplify the complex problems, Xiao Rookie, fighting!　　

Classic sql-Get the previous and next articles in the current article