oracle中可以使用start with connect by 查询树形集合,
用来查询层级关系很是好用
--当模糊查询出来的结果父级重复时可以去重即可
--查询父级(包含自己)
SELECT DISTINCT ID,PID,NAME FROM TABLE_NAME A
START WITH EXISTS ( SELECT ID FROM TABLE_NAME WHERE NAME_TYPE = '5' --做层级限制
AND name LIKE '%于家%' AND A.ID=ID ) CONNECT BY PRIOR pid = id ORDER BY DEPTTYPE
--查询子集(包含自己)
SELECT DISTINCT ID,PID,NAME FROM TABLE_NAME A
START WITH EXISTS ( SELECT ID FROM TABLE_NAME WHERE NAME_TYPE = '5'
AND name LIKE '%于家%' AND A.ID=ID ) CONNECT BY PRIOR id= pid ORDER BY DEPTTYPE
父级子集union即可得到整个树了
开发中遇到了这个需求,想了半天,没想出其他查询方法。仅以此记录