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 = @columnID	UNION ALL	SELECT p.ColumnName, p.ColumnID, p.T_C_ColumnID	FROM T_Column  P  INNER JOIN	Col_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;	}

?

?