34,838
社区成员
发帖
与我相关
我的任务
分享【100分秒结】cte 里面 的一个错误帮修正
刚刚在看帖子帮别人解决问题的时候，发现这么一个奇怪的错误，具体看代码最后的那一条语句！
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([code] varchar(50),[code2] varchar(50))
insert [tb]
select 'a1,a2','a1a1a1a1,a2a2a2a2' union all
select 'b1,b2','b1b1b1b1,b2b2b2b2'
go
with cte as
(
select [code],
SUBSTRING(t.[code2], number ,CHARINDEX(',',t.[code2]+',',number)-number) as code2
from [tb] t,master..spt_values s
where s.number >=1
and s.type = 'P'
and SUBSTRING(','+t.[code2],s.number,1) = ','
)
,cte1 as
(
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) = ','
)
select code ,code2, charindex(code,code2)from cte1
--select code ,code2 from cte1 where charindex(code,code2)>0
---最后查询的时候 上面一条语句执行没有问题，where 条件加在后面时， 出现这个莫名其妙的错误
-- 下面的会报错 消息 537，级别 16，状态 2，第 2 行
--传递给 LEFT 或 SUBSTRING 函数的长度参数无效。
...全文
请发表友善的回复…
发表回复
發糞塗牆 2014-09-23
- 打赏
- 举报
最近看《防御式编程》的书，里面很多地方说道不要“以为where条件会按照你的写法的顺序来运行”（大概这个意思），如果你真的要按顺序，常规做法是用case when来规定顺序，某些特殊情况下可以借助索引来把处理的优先级提高
霜寒月冷 2014-09-23
- 打赏
- 举报
版主说的有点道理，cte里面的执行顺序真是令人费解，我在看看其他的人的意见，再结贴！
xiaodongni 2014-09-23
- 打赏
- 举报
但是这个报错内容还是让我不理解。看这个报错。应该是SUBSTRING发生溢出啊。为啥不加charindex
不报错。加了就溢出。
xiaodongni 2014-09-23
- 打赏
- 举报
感谢你的回复，不过你解释了一堆貌似没什么用。换成*查询又如何，后面加条件还是会...
[/quote]
不好意思 我看错了。你这个报错和我的一样。但是写法不一样。
你先把最后的CTE1结果插入到一个表里面。然后再从这个里面进行SELECT就不会报错。
所以 我觉得原因应该是CTE不会提高性能。只能方便阅读。
所以 SUBSTRING(','+t.[code],s.number,1) = ',' 这个条件和CHARINDEX(code,code2)>0
没有先后问题。
霜寒月冷 2014-09-23
- 打赏
- 举报
感谢你的回复，不过你解释了一堆貌似没什么用。换成*查询又如何，后面加条件还是会...
發糞塗牆 2014-09-23
- 打赏
- 举报
另外只有charindex(code,code2)=0才报错，=1不抱错
發糞塗牆 2014-09-23
- 打赏
- 举报
反复测了一下，并且google了一下，只能说这是编译的过程中顺序并不是你写法那样，即使我再用一个cte包住同样出错，最后如果我把select code ,code2, charindex(code,code2) as c into #t from cte1
然后再
select * from #t where c>0就不抱错了，因为不需要重新计算charindex(code,code2)
xiaodongni 2014-09-23
- 打赏
- 举报
你把你的CTE1里面的SELECT 换成* 看看CTE1 你就懂了。为啥这个不能用上面的CHARINDEX。
xiaodongni 2014-09-23
- 打赏
- 举报
我也有啊。这个帖子我也试过了。一样的。 这个例子 需要对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
reenjie 2014-09-23
- 打赏
- 举报
在递归 CTE 中使用分析函数可能会出现问题，具体请参考下列连接第k点。不知道是否有用。
http://msdn.microsoft.com/zh-cn/library/ms175972.aspx
霜寒月冷 2014-09-23
- 打赏
- 举报
说的在理
LongRui888 2014-09-23
- 打赏
- 举报
如果先把结果集放到临时表，然后再去查询临时表，那么就不会有这个问题了
LongRui888 2014-09-23
- 打赏
- 举报
出现这个问题，我觉得说明实际在执行的时候，并没有严格按照 语句的顺序来执行，也就是会把最外层的where查询条件，直接放到了内部去了。
不是在内存结果计算出来后，再过滤数据，而是在计算过程中，就过滤了数据，才导致这种错误的
LongRui888 2014-09-23
- 打赏
- 举报
版主说的有点道理，cte里面的执行顺序真是令人费解，我在看看其他的人的意见，再结贴！[/quote]
这个问题，上次在回一个帖子时也有这个问题，只是 这个拆分字符串的语句，不是写在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 函数的长度参数无效。
*/
还在加载中灬 2014-09-23
- 打赏
- 举报
研究了下，好奇葩，查了一些资料，都没有结果，关注一下
