因业务需要,t_table表里面会有id, parent_id字段,设计之初是考虑业务的层次划分。
查询的时候为了满足查询到的多个节点反推查其向上的所有父节点,参考网上多个版本,最终实现如下sql:
SELECT t1.*
FROM
(
SELECT @ids AS _ids,
(SELECT @ids := GROUP_CONCAT(parent_id) FROM t_table
WHERE FIND_IN_SET(id, @ids)) AS cids
FROM t_table,
(SELECT @ids := '1,2') b
WHERE @ids IS NOT NULL) t,
t_table t1
WHERE
FIND_IN_SET(t1.id, t._ids)