日期:2014-05-18 浏览次数:20425 次
表结构如下 create table #imgType(id int,pid int,ReportToDepth int,ReportToPath varchar(50)) insert into #imgType select 1 ,0 ,0,'/1/' union all select 2 ,0 ,0, '/2/' union all select 5 ,2 ,1, '/2/5/' union all select 30,2 ,1, '/2/30/' union all select 6 ,5 ,2, '/2/5/6/' union all select 9 ,6 ,3, '/2/5/6/9/' union all select 17,9 ,4, '/2/5/6/9/17/' union all select 16,17,5, '/2/5/6/9/17/16/' union all select 4 ,1 ,1, '/1/4/' union all select 10,4 ,2, '/1/4/10/'
select id, ( select sum(case when charindex(i.ReportToPath,ii.ReportToPath)>0 then 1 else 0 end) from #imgType ii ) as a from #imgType i where pid=0
select id, (select sum(1) from #imgType ii where charindex(i.ReportToPath,ii.ReportToPath)>0 ) as a from #imgType i where pid=0 id a ----------- ----------- 1 3 2 7 (2 row(s) affected)
------解决方案--------------------
declare @TableA table (id int,pid int,ReportToDepth int,ReportToPath varchar(50)) insert into @TableA select 1 ,0 ,0,'/1/' union all select 2 ,0 ,0, '/2/' union all select 5 ,2 ,1, '/2/5/' union all select 30,2 ,1, '/2/30/' union all select 6 ,5 ,2, '/2/5/6/' union all select 9 ,6 ,3, '/2/5/6/9/' union all select 17,9 ,4, '/2/5/6/9/17/' union all select 16,17,5, '/2/5/6/9/17/16/' union all select 4 ,1 ,1, '/1/4/' union all select 10,4 ,2, '/1/4/10/' select * from @TableA select id, (select sum(1) from @TableA ii where charindex(i.ReportToPath,ii.ReportToPath)>0 ) as a from @TableA i where pid=0 --id a --1 3 --2 7
------解决方案--------------------
和这个帖是一个意思吗?
http://topic.csdn.net/u/20120306/13/ff17602b-7722-4491-b32c-b4480eaabae9.html?48317
------解决方案--------------------
N' 代表 这值对应的列是 nvarchar 类型的。