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

本文主要是介绍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 &lt; q.n2
order by m.RowNum desc
limit 1

改为

m.RowNum &gt; q.n2
limit 1
————————————————

原文链接：https://blog.csdn.net/weixin_43694038/article/details/104917053

自己的应用（城市大脑防返贫可视化）：

IF QTYPE_6 = '1' THEN -- 上一个

SELECT
max(m.ID) into temp_id
FROM
( SELECT T.ID,( @i := @i + 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 @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
max(m.ID) into temp_id
FROM
( SELECT T.ID,( @i := @i + 1 ) AS RowNum
FROM (SELECT A.ID
FROM TBL_FZFP_APPLAY_AA01 A
WHERE A.AZC005 LIKE concat(3601 , '%')
AND A.AFFIRM_TYPE <> '0'
AND A.STATUS = '1'
AND A.AAR040 = '2021'
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(3601 , '%')
AND A.AFFIRM_TYPE <> '0'
AND A.STATUS = '1'
AND A.AAR040 = '2021'
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;

这篇关于mysql排序后获取上一条记录和下一条记录的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！