mysql获取满足条件的相邻数据

Question

id type
1 stu
...
1111 stu
1112 tec
1113 stu
1114 stu
1115 stu
1116 stu
1117 tec
1118 tec
1119 stu
1120 stu
...
9999 stu

获取满足条件 type = stu 的相邻记录
where type = stu and id > 1113 order by id asc limit 3
可以获取 3 条;
where type = stu and id < 1113 order by id dese limit 3
只能获取 1 条;

怎样组合两句，获取满足条件的相邻6条记录？最终获取id为1113相邻的：1111/1114/1115/1116/1119/1120

Answer 1

where type = stu and id != 3 order by id asc

这样？

Answer 2

select id from 
(select id from test where type = stu and id > 1113 order by id asc limit 5
union all
select id from test where type = stu and id < 1113 order by id desc limit 5) 
limit 6;

Answer 3

可以试试绝对值ABS()：

SELECT
* 
FROM
table_t
WHERE type = stu
ORDER BY ABS(id - 1113)
LIMIT 6
;

希望能帮助到你。