问个子查询有关问题

2012-04-22

问个子查询问题SQL code表结构如下create table #imgType(id int,pid int,ReportToDepth int,ReportToPath

问个子查询问题

SQL code

表结构如下create table #imgType(id int,pid int,ReportToDepth int,ReportToPath varchar(50))insert into #imgTypeselect 1 ,0 ,0,'/1/' union allselect 2 ,0 ,0, '/2/' union allselect 5 ,2 ,1, '/2/5/' union allselect 30,2 ,1, '/2/30/' union allselect 6 ,5 ,2, '/2/5/6/' union allselect 9 ,6 ,3, '/2/5/6/9/' union allselect 17,9 ,4, '/2/5/6/9/17/' union allselect 16,17,5, '/2/5/6/9/17/16/' union allselect 4 ,1 ,1, '/1/4/' union allselect 10,4 ,2, '/1/4/10/'

这样写会提示在包含外部引用的被聚合表达式中指定了多个列。如果被聚合的表达式包含外部引用，那么该外部引用就必须是该表达式中所引用的唯一的一列。
该怎么改呢？

SQL code

select id,        (        select sum(case when charindex(i.ReportToPath,ii.ReportToPath)>0 then 1 else 0 end)         from #imgType ii        ) as afrom #imgType i where pid=0

[解决办法]
try this,

SQL code

select id,(select sum(1) from #imgType ii where charindex(i.ReportToPath,ii.ReportToPath)>0) as afrom #imgType i where pid=0id          a----------- -----------1           32           7(2 row(s) affected)
[解决办法]
SQL codedeclare  @TableA table (id int,pid int,ReportToDepth int,ReportToPath varchar(50))insert into  @TableAselect 1 ,0 ,0,'/1/' union allselect 2 ,0 ,0, '/2/' union allselect 5 ,2 ,1, '/2/5/' union allselect 30,2 ,1, '/2/30/' union allselect 6 ,5 ,2, '/2/5/6/' union allselect 9 ,6 ,3, '/2/5/6/9/' union allselect 17,9 ,4, '/2/5/6/9/17/' union allselect 16,17,5, '/2/5/6/9/17/16/' union allselect 4 ,1 ,1, '/1/4/' union allselect 10,4 ,2, '/1/4/10/'select * from  @TableAselect id,(select sum(1) from  @TableA ii where charindex(i.ReportToPath,ii.ReportToPath)>0) as afrom  @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
[解决办法]
SQL codeN' 代表 这值对应的列是 nvarchar 类型的。

热点排行

SQL Server

问个子查询有关问题