mysql排序后获取上一条记录和下一条记录

有个需求，根据中文首字母排序后，获取上一条数据和下一条数据，
找到一篇博客给了灵感，找不到博客地址，勿怪，贴代码
以下我将sql写进了存储过程中

CREATE DEFINER=“myqsl权限名称” PROCEDURE `存储过程名称`(IN `book_id_in` int(11),IN `book_id_in_type` int(11))
BEGIN
	#book_id_in_type 1上一条 2下一条
	
	IF `book_id_in_type`=1 THEN
		SELECT
		m.bookId #我要查询的字段
	FROM
		(
		SELECT
			( @i := @i + 1 ) AS RowNum,
			A.bookId
		FROM
			books A, #要查询的表
			( SELECT @i := 0 ) B 
		WHERE 1=1 #查询条件 
			order by CONVERT( A.bookName USING gbk ) COLLATE gbk_chinese_ci
		) m,
		#下同
		(
		SELECT
			D.bookId,D.RowNum as n2
		FROM
			(
			SELECT
				( @j := @j + 1 ) AS RowNum,
				A.bookId
			FROM
				books A,
				( SELECT @j := 0 ) B 
			WHERE 1=1
				order by CONVERT( A.bookName USING gbk ) COLLATE gbk_chinese_ci
			) D 
		WHERE
			D.bookId = `book_id_in`
		) q 
	WHERE
			m.RowNum < q.n2 
			order by m.RowNum desc
			limit  1;
	END if;
	
	IF `book_id_in_type`=2 THEN
		SELECT
			m.bookId
		FROM
			(
			SELECT
				( @i := @i + 1 ) AS RowNum,
				A.bookId
			FROM
				books A,
				( SELECT @i := 0 ) B 
			WHERE 1=1
				order by CONVERT( A.bookName USING gbk ) COLLATE gbk_chinese_ci
			) m,
			(
			SELECT
				D.bookId,D.RowNum as n2
			FROM
				(
				SELECT
					( @j := @j + 1 ) AS RowNum,
					A.bookId
				FROM
					books A,
					( SELECT @j := 0 ) B 
				WHERE 1=1
					order by CONVERT( A.bookName USING gbk ) COLLATE gbk_chinese_ci
				) D 
			WHERE
				D.bookId = `book_id_in`
			) q 
		WHERE
				m.RowNum > q.n2 
				limit  1;
	END if;
	
END

根据实际业务做以下改进，将sql语句写到了mybatis中

SELECT
    m.bookId
    FROM
    (
    select ( @i := @i + 1 ) AS RowNum,o.bookId from ( SELECT
    A.bookId
    FROM
    books A
    Left Join booktype bt On A.bookId=bt.bookId
    left Join iftAgeBook fab On fab.bookId=A.bookId
    Left Join bearPalmBook bpb On A.bookId=bpb.bookId
    where A.status=1
    <if test="classify!=null and classify.size()!=0">
      and bt.booksCatsId in
      <foreach collection="classify" item="items" open="(" separator="," close=")">
        #{items}
      </foreach>
    </if>

    <if test="age!=null and age.size()!=0">
      and fab.iftAgeId in
      <foreach collection="age" item="items" open="(" separator="," close=")">
        #{items}
      </foreach>
    </if>

    <if test="bearPalm!=null and bearPalm.size()!=0">
      and bpb.bearPalmId in
      <foreach collection="bearPalm" item="items" open="(" separator="," close=")">
        #{items}
      </foreach>
    </if>

    <if test="bookName != null">
      and A.bookName like  CONCAT('%',#{bookName},'%')
    </if>

    <if test="isVoiceBand==1">
      AND (A.VoiceBandUrl is not null and A.VoiceBandUrl!='')
    </if>
    <if test="isReadVoiceBand==1">
      and A.bookId in (select vbl.bookId from voicebandbrowselog vbl where vbl.userId=#{userId})
    </if>
    <if test="isReadVoiceBand==2">
      and A.bookId not in (select vbl.bookId from voicebandbrowselog vbl where vbl.userId=#{userId})
    </if>
    GROUP BY A.bookId
    order by CONVERT( A.bookName USING gbk ) COLLATE gbk_chinese_ci) o,( SELECT @i := 0 ) B
    ) m,
    (
    SELECT
    D.bookId,D.RowNum as n2
    FROM
    (
    select ( @j := @j + 1 ) AS RowNum,o.bookId from (
    SELECT
    A.bookId
    FROM
    books A
    Left Join booktype bt On A.bookId=bt.bookId
    left Join iftAgeBook fab On fab.bookId=A.bookId
    Left Join bearPalmBook bpb On A.bookId=bpb.bookId
    where A.status=1
    <if test="classify!=null and classify.size()!=0">
      and bt.booksCatsId in
      <foreach collection="classify" item="items" open="(" separator="," close=")">
        #{items}
      </foreach>
    </if>

    <if test="age!=null and age.size()!=0">
      and fab.iftAgeId in
      <foreach collection="age" item="items" open="(" separator="," close=")">
        #{items}
      </foreach>
    </if>

    <if test="bearPalm!=null and bearPalm.size()!=0">
      and bpb.bearPalmId in
      <foreach collection="bearPalm" item="items" open="(" separator="," close=")">
        #{items}
      </foreach>
    </if>

    <if test="bookName != null">
      and A.bookName like  CONCAT('%',#{bookName},'%')
    </if>

    <if test="isVoiceBand==1">
      AND (A.VoiceBandUrl is not null and A.VoiceBandUrl!='')
    </if>
    <if test="isReadVoiceBand==1">
      and A.bookId in (select vbl.bookId from voicebandbrowselog vbl where vbl.userId=#{userId})
    </if>
    <if test="isReadVoiceBand==2">
      and A.bookId not in (select vbl.bookId from voicebandbrowselog vbl where vbl.userId=#{userId})
    </if>
    GROUP BY A.bookId
    order by CONVERT( A.bookName USING gbk ) COLLATE gbk_chinese_ci) o,( SELECT @j := 0 ) B
    ) D
    WHERE
    D.bookId = #{bookId}
    ) q
    WHERE
    m.RowNum &lt; q.n2
    order by m.RowNum desc
    limit 1

上边为上一条，
同理将

 m.RowNum < q.n2
    order by m.RowNum desc
    limit 1

改为

m.RowNum > q.n2
    limit 1

查询下一条

改动主要是将查询关联条件筛选完之后 对结果进行加序号，然后取序号的前后数据