我不是100%确定我理解了你的问题，但从我认为你试图做的是让id 1返回基于传递id 4到查询。
这个查询可以做到这一点，如果你把它变成一个存储过程并传递一个id，它将返回你需要做的任何事情所需的数据：

SELECT TOP(1) * FROM #test_table
WHERE (id = @CurrentId
OR subjectid = (SELECT subjectid FROM #test_table WHERE id = @CurrentId))
AND id < @CurrentId
ORDER BY id DESC

您可以在获取当前主题的同时检索下一个和上一个ID：

SELECT *,
    (SELECT id FROM topics WHERE subjectId = t.subjectId AND id < t.id ORDER BY id DESC LIMIT 1) AS prev,
    (SELECT id FROM topics WHERE subjectId = t.subjectId AND id > t.id ORDER BY id ASC LIMIT 1) AS next
FROM topics t
WHERE t.id = 4;

它将返回：
| 身份证|主题|subjectId|内容|上一页|下一个|
| --------------|--------------|--------------|--------------|--------------|--------------|
| 四|主题4|五|康...|1|六|

从MySQL 8.0开始，你可以用下一种方式使用窗口函数lead和lag：

select 
    id, subject_id, 
    lag(subject_id) over w as prev_subject_id, 
    lead(subject_id) over w next_subject_id
from test
window w AS (ORDER BY id asc);

https://sqlize.online/s/gi