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

財神
  • 17

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

回复
阅读 1.5k
3 个回答
where type = stu and id != 3 order by id asc

这样?

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;

可以试试绝对值ABS():

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

希望能帮助到你。

宣传栏