SQL树形构造查询ById

SQL树形结构查询ById

CREATE FUNCTION [dbo].[fn_FindColumnTree] (@columnID INTEGER)RETURNS @FindColumnTree TABLE (    ColumnID int  NOT NULL,    ColumnName varchar(256) NOT NULL,    p_id int  NULL,p_name varchar(256)  null)ASBEGIN   WITH Col_Recursive(ColumnName, ColumnID, T_C_ColumnID)     AS(SELECT ColumnName, ColumnID,T_C_ColumnID  FROM T_Column WHERE ColumnID = @columnIDUNION ALLSELECT p.ColumnName, p.ColumnID, p.T_C_ColumnIDFROM T_Column  P  INNER JOINCol_Recursive A ON A.ColumnID = P.T_C_ColumnID )-- copy the required columns to the result of the function    INSERT @FindColumnTree   SELECT a.ColumnID,a.ColumnName,a.T_C_ColumnID,b.ColumnName as p_name   FROM Col_Recursive as a left join T_Column as b on a.T_C_ColumnID=b.ColumnID   RETURNEND;

?

?

其中ColumnName 代表栏目名字 ColumnID 栏目 ID T_C_ColumnID 父栏目Id

usage:

?

public List<Node> buildTree(String rootId) {final String sql="select ColumnID, ColumnName ,T_C_ColumnID FROM T_Column where ColumnID in (select ColumnID from dbo.fn_FindColumnTree("+rootId+"))";     //sql调用存储过程查询树的语句.List<Node> result = new ArrayList<Node>();List tree=this.hibernateTemplate.executeFind(new HibernateCallback(){public Object doInHibernate(Session session)throws HibernateException, SQLException {return session.createSQLQuery(sql).list();}});for (Iterator iterator = tree.iterator(); iterator.hasNext();) {Node node = new Node();Object[] obj = (Object[]) iterator.next();node.setId(obj[0].toString());node.setName(obj[1].toString());node.setPid(obj[2]!=null?obj[2].toString():"0");node.setUrl("http://www.baidu.com");if(node.getId().equals(rootId)){node.setPid("0");}result.add(node);}return result;}

?

?