【100分秒结】cte 里面 的一个错误帮修正

Question

我也有啊。这个帖子我也试过了。一样的。 这个例子 需要对2列分别进行拆分。
必须要分别拆分。然后进行连接。不能一起。
我当时 代码这样
select code2,
       SUBSTRING(t.[code], number ,CHARINDEX(',',t.[code]+',',number)-number) as code  
from cte t,master..spt_values s
where s.number >=1
and s.type = 'P'
and SUBSTRING(','+t.[code],s.number,1) = ','   or SUBSTRING(','+t.[code2],s.number,1) = ',' 
一样报这个错误。因为对于CODE2来说他的CHARINDEX(',',t.[code2]+',',number)=10，
用来来截断CODE 就会报错啊LEN（code)<10

Answer 1

你把你的CTE1里面的SELECT 换成*  看看CTE1 你就懂了。为啥这个不能用上面的CHARINDEX。

Answer 2

反复测了一下，并且google了一下，只能说这是编译的过程中顺序并不是你写法那样，即使我再用一个cte包住同样出错，最后如果我把select code ,code2, charindex(code,code2)  as c into #t from cte1   
然后再
select * from #t where c>0就不抱错了，因为不需要重新计算charindex(code,code2)

Answer 3

另外只有charindex(code,code2)=0才报错，=1不抱错

Answer 4

感谢你的回复，不过你解释了一堆貌似没什么用。换成*查询又如何，后面加条件还是会...
不好意思 我看错了。你这个报错和我的一样。但是写法不一样。
 你先把最后的CTE1结果插入到一个表里面。然后再从这个里面进行SELECT就不会报错。
所以 我觉得原因应该是CTE不会提高性能。只能方便阅读。
所以 SUBSTRING(','+t.[code],s.number,1) = ','  这个条件和CHARINDEX(code,code2)>0
没有先后问题。

Answer 5

但是这个报错内容还是让我不理解。看这个报错。应该是SUBSTRING发生溢出啊。为啥不加charindex 
不报错。加了就溢出。

Answer 6

版主说的有点道理，cte里面的执行顺序真是令人费解，我在看看其他的人的意见，再结贴！

Answer 7

最近看《防御式编程》的书，里面很多地方说道不要“以为where条件会按照你的写法的顺序来运行”（大概这个意思），如果你真的要按顺序，常规做法是用case when来规定顺序，某些特殊情况下可以借助索引来把处理的优先级提高

Answer 8

版主说的有点道理，cte里面的执行顺序真是令人费解，我在看看其他的人的意见，再结贴！这个问题，上次在回一个帖子时也有这个问题，只是 这个拆分字符串的语句，不是写在cte中的，而是单个语句，就会报错。这个语句没有问题：
select 
       v,
       SUBSTRING(t.v, number ,CHARINDEX(',',t.v+',',number)-number)
from 
(
select   '1,2,3,4,4.5,6' v 
union all
select '1.5,2,3,4,4.5,6.6'
)t ,master..spt_values s
where s.number >=1
and s.type = 'P'
and SUBSTRING(','+t.v,s.number,1) = ','
然后现在需要把 字符串中的小数 过滤掉，就有问题了，会报错：
select  *
from 
( 
select 
   v,
   SUBSTRING(t.v, number ,CHARINDEX(',',t.v+',',number)-number) as vv
from 
(
select   '1,2,3,4,4.5,6' v 
union all
select '1.5,2,3,4,4.5,6.6'
)t ,master..spt_values s
where s.number >=1
and s.type = 'P'
and SUBSTRING(','+t.v,s.number,1) = ','
)t
where vv not like '%.%'
/*消息 537，级别 16，状态 2，第 3 行
传递给 LEFT 或 SUBSTRING 函数的长度参数无效。*/

Answer 9

出现这个问题，我觉得说明实际在执行的时候，并没有严格按照 语句的顺序来执行，也就是会把最外层的where查询条件，直接放到了内部去了。不是在内存结果计算出来后，再过滤数据，而是在计算过程中，就过滤了数据，才导致这种错误的

Answer 10

在递归 CTE 中使用分析函数可能会出现问题，具体请参考下列连接第k点。不知道是否有用。
http://msdn.microsoft.com/zh-cn/library/ms175972.aspx