Get the UUID of mysql previous article next article (previous article next article) application examples

First talk about the principle:

There is the previous article and the next article (the previous article and the next article), which must be an effect in a query set (list) under the search conditions and the fixed sorting rules.
1. We use rownum (a self-increasing label) for the query result set under these two conditions (search conditions where and order)
2. Query the rownum value x of the target uuid.
3. Query the previous and next One: uuid with rownum = x-1 to get the previous uuid with rownum = x+1 to get the next

Practical application:

A fixed search condition and fixed sorting query:

SELECT
	bn.*
FROM
	base_notice bn
LEFT JOIN (
	SELECT
		GROUP_CONCAT(userinfo.STR_NAME) AS toUserNames,
		GROUP_CONCAT(touser.STR_GUID) AS addresseeGuids,
		GROUP_CONCAT(touser.STR_USER) AS touserIds,
		GROUP_CONCAT(touser.is_read) AS isReads,
		notice.STR_GUID AS noticeId
	FROM
		base_notice notice
	LEFT JOIN base_notice_addressee touser ON touser.STR_NOTICE_GUID = notice.STR_GUID
	LEFT JOIN base_user userinfo ON touser.str_user = userinfo.STR_GUID
	WHERE
		notice.STR_CREATE_USER_ID = '1367646365085077505'
	AND notice.STR_TYPE = 2
	AND (
		touser.IS_DELETE = 0
		AND touser.IS_ENABLE = 1
	)
	GROUP BY
		notice.STR_GUID
) tempT1 ON bn.STR_GUID = tempT1.noticeId
WHERE
	bn.IS_DELETE = 0
AND bn.IS_ENABLE = 1
AND bn.STR_CREATE_USER_ID = '1367646365085077505'
ORDER BY
	bn.DT_GET_DATE DESC
Insert picture description here


Increase rownum: (compare the above sql and the following sql add-marked operation)
Description: In actual applications, usually get the uuid of the previous one and the next one. Here is my result set. After I added rowNum, I only queried the
code of uuid and only observed The actual operation of the marked position is attached below the effect and result without "–":

-- SELECT
-- 	rownum AS rowNum,
-- 	STR_GUID AS strGuid
-- FROM
-- 	(
		SELECT
-- 			@rownum :[email protected] + 1 AS rownum,
			bn.*
		FROM
-- 			(SELECT @rownum := 0) r,
			base_notice bn
		LEFT JOIN (
			SELECT
				GROUP_CONCAT(userinfo.STR_NAME) AS toUserNames,
				GROUP_CONCAT(touser.STR_GUID) AS addresseeGuids,
				GROUP_CONCAT(touser.STR_USER) AS touserIds,
				GROUP_CONCAT(touser.is_read) AS isReads,
				notice.STR_GUID AS noticeId
			FROM
				base_notice notice
			LEFT JOIN base_notice_addressee touser ON touser.STR_NOTICE_GUID = notice.STR_GUID
			LEFT JOIN base_user userinfo ON touser.str_user = userinfo.STR_GUID
			WHERE
				notice.STR_CREATE_USER_ID = '1367646365085077505'
			AND notice.STR_TYPE = 2
			AND (
				touser.IS_DELETE = 0
				AND touser.IS_ENABLE = 1
			)
			GROUP BY
				notice.STR_GUID
		) tempT1 ON bn.STR_GUID = tempT1.noticeId
		WHERE
			bn.IS_DELETE = 0
		AND bn.IS_ENABLE = 1
		AND bn.STR_CREATE_USER_ID = '1367646365085077505'
		ORDER BY
			bn.DT_GET_DATE DESC
-- 	) tempT
 SELECT
 	rownum AS rowNum,
 	STR_GUID AS strGuid
 FROM
 	(
		SELECT
 			@rownum :[email protected] + 1 AS rownum,
			bn.*
		FROM
 			(SELECT @rownum := 0) r,
			base_notice bn
		LEFT JOIN (
			SELECT
				GROUP_CONCAT(userinfo.STR_NAME) AS toUserNames,
				GROUP_CONCAT(touser.STR_GUID) AS addresseeGuids,
				GROUP_CONCAT(touser.STR_USER) AS touserIds,
				GROUP_CONCAT(touser.is_read) AS isReads,
				notice.STR_GUID AS noticeId
			FROM
				base_notice notice
			LEFT JOIN base_notice_addressee touser ON touser.STR_NOTICE_GUID = notice.STR_GUID
			LEFT JOIN base_user userinfo ON touser.str_user = userinfo.STR_GUID
			WHERE
				notice.STR_CREATE_USER_ID = '1367646365085077505'
			AND notice.STR_TYPE = 2
			AND (
				touser.IS_DELETE = 0
				AND touser.IS_ENABLE = 1
			)
			GROUP BY
				notice.STR_GUID
		) tempT1 ON bn.STR_GUID = tempT1.noticeId
		WHERE
			bn.IS_DELETE = 0
		AND bn.IS_ENABLE = 1
		AND bn.STR_CREATE_USER_ID = '1367646365085077505'
		ORDER BY
			bn.DT_GET_DATE DESC
 	) tempT
Insert picture description here

Then suppose we need to query the previous and next
items of uuid = "100", just add the where condition after the above sql to query the rowNum value of where strGuid = '100'.

  where tempT.STR_GUID='100'
Insert picture description here


We see that strGuid='100' his rownum=2 is very easy, we know his previous rowNum=1, the next rowNum=3,
so we modify the conditions of the above sql statement

   where rownum in (1,3);
Insert picture description here


We can see from the result set: the previous uuid and the next uui are all

The rownum=1 of the currently checked uuid is: the result set of the first item

where rownum in (0,2);
Insert picture description here


The rownum=3 of the currently checked uuid is: the result set of the last one

where rownum in (2,4);
Insert picture description here

How to distinguish the first and last one?

We use the query condition currently checked uuid rownum=x
Get the result set rownum= x-1 strGuid is not null ie: the previous one has
a value Get the result set rownum= x+1 strGuid is not null ie: the next one has a value
Previous is null Next is non-null: The current is the first previous Previous is non-null Next is non-null: The middle of the non-headline and the last one Previous is non-null
Next is null: The current is the last previous Previous is null Next is null : Query records that are not within the current conditions.
Previous uuidNext uuidConclusion of the currently queried uuid
nullNot nullArticle 1
Not nullNot nullThe one in the middle between the non-headline and the last article
Not nullnullLast one
nullnull: Query records in non-current conditions

the above. Have you lost your studies! ?