有个需求，根据中文首字母排序后，获取上一条数据和下一条数据，
找到一篇博客给了灵感，找不到博客地址，勿怪，贴代码
以下我将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 < 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
————————————————
自己的应用（城市大脑防返贫可视化）：
IF QTYPE_6 = '1' THEN -- 上一个
SELECT
m.ID,m.AAA001 into temp_id,temp_AAA001
FROM
( SELECT T.ID,t.AAA001,( @i := @i + 1 ) AS RowNum
FROM (SELECT A.ID,A.AAA001
FROM TBL_FZFP_APPLAY_AA01 A
WHERE A.AZC005 LIKE concat(GET_AREACODE(QAREACODE), '%')
AND A.AFFIRM_TYPE <> '0'
AND A.STATUS = '1'
AND A.AAR040 = QYEAR
AND (A.in_from = QTYPE_1 or QTYPE_1 ='')
AND (A.STATE in (select SUBSTRING_INDEX(SUBSTRING_INDEX(QTYPE_2,',',help_topic_id+1),',',-1) COLUMN_VALUE from mysql.help_topic where help_topic_id <= length(QTYPE_2)-length(replace(QTYPE_2,',','')) order by help_topic_id ) or QTYPE_2 ='')
AND (A.STATE not in (select SUBSTRING_INDEX(SUBSTRING_INDEX(QTYPE_2_2,',',help_topic_id+1),',',-1) COLUMN_VALUE from mysql. help_topic where help_topic_id <= length(QTYPE_2_2)-length(replace(QTYPE_2_2,',','')) order by help_topic_id ) or QTYPE_2_2 ='')
AND (A.STATE in (select SUBSTRING_INDEX(SUBSTRING_INDEX(QTYPE_3,',',help_topic_id+1),',',-1) COLUMN_VALUE from mysql.help_topic where help_topic_id <= length(QTYPE_3)-length(replace(QTYPE_3,',','')) order by help_topic_id ) or QTYPE_3 ='')
AND (A.AFFIRM_TYPE = QTYPE_4 or QTYPE_4 ='')
AND (A.AFFIRM_TYPE in (select SUBSTRING_INDEX(SUBSTRING_INDEX(QTYPE_5,',',help_topic_id+1),',',-1) COLUMN_VALUE from mysql. help_topic where help_topic_id <= length(QTYPE_5)-length(replace(QTYPE_5,',','')) order by help_topic_id ) or QTYPE_5 ='')
order by A.AZC005, A.CREATE_TIME DESC) T, ( SELECT @i := 0 ) B
) m,
( SELECT D.RowNum as n2 FROM
( SELECT T.ID,( @j := @j + 1 ) AS RowNum
FROM (SELECT A.ID
FROM TBL_FZFP_APPLAY_AA01 A
WHERE A.AZC005 LIKE concat(GET_AREACODE(QAREACODE), '%')
AND A.AFFIRM_TYPE <> '0'
AND A.STATUS = '1'
AND A.AAR040 = QYEAR
AND (A.in_from = QTYPE_1 or QTYPE_1 ='')
AND (A.STATE in (select SUBSTRING_INDEX(SUBSTRING_INDEX(QTYPE_2,',',help_topic_id+1),',',-1) COLUMN_VALUE from mysql.help_topic where help_topic_id <= length(QTYPE_2)-length(replace(QTYPE_2,',','')) order by help_topic_id ) or QTYPE_2 ='')
AND (A.STATE not in (select SUBSTRING_INDEX(SUBSTRING_INDEX(QTYPE_2_2,',',help_topic_id+1),',',-1) COLUMN_VALUE from mysql. help_topic where help_topic_id <= length(QTYPE_2_2)-length(replace(QTYPE_2_2,',','')) order by help_topic_id ) or QTYPE_2_2 ='')
AND (A.STATE in (select SUBSTRING_INDEX(SUBSTRING_INDEX(QTYPE_3,',',help_topic_id+1),',',-1) COLUMN_VALUE from mysql.help_topic where help_topic_id <= length(QTYPE_3)-length(replace(QTYPE_3,',','')) order by help_topic_id ) or QTYPE_3 ='')
AND (A.AFFIRM_TYPE = QTYPE_4 or QTYPE_4 ='')
AND (A.AFFIRM_TYPE in (select SUBSTRING_INDEX(SUBSTRING_INDEX(QTYPE_5,',',help_topic_id+1),',',-1) COLUMN_VALUE from mysql. help_topic where help_topic_id <= length(QTYPE_5)-length(replace(QTYPE_5,',','')) order by help_topic_id ) or QTYPE_5 ='')
order by A.AZC005, A.CREATE_TIME DESC) T, ( SELECT @j := 0 ) B ) D
WHERE D.ID = QID
) q
WHERE
m.RowNum < q.n2
order by m.RowNum desc
limit 1;
ELSEIF QTYPE_6 = '2' then -- 下一个
SELECT
m.ID,m.AAA001 into temp_id,temp_AAA001
FROM
( SELECT T.ID,t.AAA001,( @i := @i + 1 ) AS RowNum
FROM (SELECT A.ID,A.AAA001
FROM TBL_FZFP_APPLAY_AA01 A
WHERE A.AZC005 LIKE concat(GET_AREACODE(QAREACODE), '%')
AND A.AFFIRM_TYPE <> '0'
AND A.STATUS = '1'
AND A.AAR040 = QYEAR
AND (A.in_from = QTYPE_1 or QTYPE_1 ='')
AND (A.STATE in (select SUBSTRING_INDEX(SUBSTRING_INDEX(QTYPE_2,',',help_topic_id+1),',',-1) COLUMN_VALUE from mysql.help_topic where help_topic_id <= length(QTYPE_2)-length(replace(QTYPE_2,',','')) order by help_topic_id ) or QTYPE_2 ='')
AND (A.STATE not in (select SUBSTRING_INDEX(SUBSTRING_INDEX(QTYPE_2_2,',',help_topic_id+1),',',-1) COLUMN_VALUE from mysql. help_topic where help_topic_id <= length(QTYPE_2_2)-length(replace(QTYPE_2_2,',','')) order by help_topic_id ) or QTYPE_2_2 ='')
AND (A.STATE in (select SUBSTRING_INDEX(SUBSTRING_INDEX(QTYPE_3,',',help_topic_id+1),',',-1) COLUMN_VALUE from mysql.help_topic where help_topic_id <= length(QTYPE_3)-length(replace(QTYPE_3,',','')) order by help_topic_id ) or QTYPE_3 ='')
AND (A.AFFIRM_TYPE = QTYPE_4 or QTYPE_4 ='')
AND (A.AFFIRM_TYPE in (select SUBSTRING_INDEX(SUBSTRING_INDEX(QTYPE_5,',',help_topic_id+1),',',-1) COLUMN_VALUE from mysql. help_topic where help_topic_id <= length(QTYPE_5)-length(replace(QTYPE_5,',','')) order by help_topic_id ) or QTYPE_5 ='')
order by A.AZC005, A.CREATE_TIME DESC) T, ( SELECT @i := 0 ) B
) m,
( SELECT D.RowNum as n2 FROM
( SELECT T.ID,( @j := @j + 1 ) AS RowNum
FROM (SELECT A.ID
FROM TBL_FZFP_APPLAY_AA01 A
WHERE A.AZC005 LIKE concat(GET_AREACODE(QAREACODE), '%')
AND A.AFFIRM_TYPE <> '0'
AND A.STATUS = '1'
AND A.AAR040 = QYEAR
AND (A.in_from = QTYPE_1 or QTYPE_1 ='')
AND (A.STATE in (select SUBSTRING_INDEX(SUBSTRING_INDEX(QTYPE_2,',',help_topic_id+1),',',-1) COLUMN_VALUE from mysql.help_topic where help_topic_id <= length(QTYPE_2)-length(replace(QTYPE_2,',','')) order by help_topic_id ) or QTYPE_2 ='')
AND (A.STATE not in (select SUBSTRING_INDEX(SUBSTRING_INDEX(QTYPE_2_2,',',help_topic_id+1),',',-1) COLUMN_VALUE from mysql. help_topic where help_topic_id <= length(QTYPE_2_2)-length(replace(QTYPE_2_2,',','')) order by help_topic_id ) or QTYPE_2_2 ='')
AND (A.STATE in (select SUBSTRING_INDEX(SUBSTRING_INDEX(QTYPE_3,',',help_topic_id+1),',',-1) COLUMN_VALUE from mysql.help_topic where help_topic_id <= length(QTYPE_3)-length(replace(QTYPE_3,',','')) order by help_topic_id ) or QTYPE_3 ='')
AND (A.AFFIRM_TYPE = QTYPE_4 or QTYPE_4 ='')
AND (A.AFFIRM_TYPE in (select SUBSTRING_INDEX(SUBSTRING_INDEX(QTYPE_5,',',help_topic_id+1),',',-1) COLUMN_VALUE from mysql. help_topic where help_topic_id <= length(QTYPE_5)-length(replace(QTYPE_5,',','')) order by help_topic_id ) or QTYPE_5 ='')
order by A.AZC005, A.CREATE_TIME DESC) T, ( SELECT @j := 0 ) B ) D
WHERE D.ID = QID
) q
WHERE
m.RowNum > q.n2
limit 1;
END IF;
