你还得写一个split函数,以分割分隔符. Split如下ALTER FUNCTION [dbo].[Split](@Expression VARCHAR(MAX),@Delimiter varchar(10)) RETURNS Table AS
RETURN( WITH L0 AS(SELECT 1 AS n UNION ALL SELECT 1), L1 AS(SELECT 1 AS n FROM L0 a,L0 b), L2 AS(SELECT 1 AS n FROM L1 a,L1 b), L3 AS(SELECT 1 AS n FROM L2 a,L2 b), L4 AS(SELECT 1 AS n FROM L3 a,L3 b), L5 AS(SELECT 1 AS n FROM L4 a,L4 b), cte2 AS(SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS n FROM L4) SELECT SUBSTRING(@Delimiter+@Expression+@Delimiter,n+LEN(@Delimiter), CHARINDEX(@Delimiter,@Delimiter+@Expression+@Delimiter,n+LEN(@Delimiter)) -n-LEN(@Delimiter) ) AS list, ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS LEVEL FROM cte2 n WHERE SUBSTRING(@Delimiter+@Expression+@Delimiter,n,LEN(@Delimiter))=@Delimiter AND n.n<=LEN(@Expression)+1 AND isnull(@Expression,'')<>'' AND ISNULL(@Delimiter,'')<>''假设指定的ID为@ID,级别为@Level,表为table 求最长层级这样写: select top 1 * from table a cross apply split(a.path,'/') b where path like '%/'+@ID+'/%' order by b.level desc --求指定级别这样写 select top * from table a cross apply split(a.path,'/') b where path like '%/'+@ID+'/%' and level=@level
如果表里是这样存放的,
那么,ORACLSE有专门的语法,
SQLServer2005以上也有专门的语法,
一个SQL语句就可以找到指定ID的所有的下级ID,
那么,找出来的行数,就是你要的级数。
对于一个会员(或者叫参考点)当下级数量不确定,而且其下级也具有下级时,是否也能够查询得出该参考点下面的层数。
能否给个详细说明
具体化路径的做法是,在1楼的基础上新增一个path字段,保存节点的路径,并建立索引.其后则只需要对这个路径进行查询.
不过在修改节点的时候要维护好这个path字段.
请问,Path字段是保存什么内容的,是上级的信息吗,还是其他信息??Path全局是唯一的吗??
示例数据
ID PID PATH
1 /1/
2 1 /1/2/
3 1 /1/3/
4 2 /1/2/4/
ID PID PATH
1 NULL /1/
2 1 /1/2/
3 1 /1/3/
4 2 /1/2/4/
我准备试一下
烦请告知,在以一个会员为参照时,如何用Path来找出查找其下最长层级和指定级数的会员?SQL 怎么写?
Split如下ALTER FUNCTION [dbo].[Split](@Expression VARCHAR(MAX),@Delimiter varchar(10))
RETURNS Table
AS
RETURN(
WITH
L0 AS(SELECT 1 AS n UNION ALL SELECT 1),
L1 AS(SELECT 1 AS n FROM L0 a,L0 b),
L2 AS(SELECT 1 AS n FROM L1 a,L1 b),
L3 AS(SELECT 1 AS n FROM L2 a,L2 b),
L4 AS(SELECT 1 AS n FROM L3 a,L3 b),
L5 AS(SELECT 1 AS n FROM L4 a,L4 b),
cte2 AS(SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS n FROM L4)
SELECT SUBSTRING(@Delimiter+@Expression+@Delimiter,n+LEN(@Delimiter),
CHARINDEX(@Delimiter,@Delimiter+@Expression+@Delimiter,n+LEN(@Delimiter)) -n-LEN(@Delimiter) ) AS list,
ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS LEVEL
FROM cte2 n
WHERE SUBSTRING(@Delimiter+@Expression+@Delimiter,n,LEN(@Delimiter))=@Delimiter
AND n.n<=LEN(@Expression)+1
AND isnull(@Expression,'')<>''
AND ISNULL(@Delimiter,'')<>''假设指定的ID为@ID,级别为@Level,表为table
求最长层级这样写:
select top 1 *
from table a cross apply split(a.path,'/') b
where path like '%/'+@ID+'/%'
order by b.level desc
--求指定级别这样写
select top *
from table a cross apply split(a.path,'/') b
where path like '%/'+@ID+'/%'
and level=@level