SQL树形结构查询ById

最新推荐文章于 2024-09-18 18:19:46 发布

原创最新推荐文章于 2024-09-18 18:19:46 发布 · 255 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#SQL #C #C++ #C#

本文介绍了一种使用SQL递归公用表表达式（CTE）实现的树形结构查询方法，并通过具体示例展示了如何构建树形数据。该方法适用于需要展示层级结构信息的应用场景。

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
)
AS
BEGIN
   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
   RETURN
END;

其中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;
	}