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

⋅Jquery checkbox 选择问题. ⋅[80]更新语句 ⋅【100分秒结】cte 里面的一个错误帮修正 更多帖子 关注空间博客 霜寒月冷 本版专家分：3772 结帖率 100%

刚刚在看帖子帮别人解决问题的时候，发现这么一个奇怪的错误，具体看代码最后的那一条语句！

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 函数的长度参数无效。

0 2014-09-23 10:40:34

回复数 16 只看楼主引用举报楼主

⋅SQL 2000 配置邮件服务器 ⋅数据库表数据和EXCEL之间的转换 ⋅SQL 2000 作业 DTS 更多帖子 关注空间博客 xiaodongni 本版专家分：5625 黄花 2014年10月 MS-SQL Server大版内专家分月排行榜第二: 我也有啊。这个帖子我也试过了。一样的。这个例子需要对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

0 2014-09-23 11:12:38

只看TA 引用举报 #1    得分 1

⋅SQL 2000 配置邮件服务器 ⋅数据库表数据和EXCEL之间的转换 ⋅SQL 2000 作业 DTS 更多帖子 关注空间博客 xiaodongni 本版专家分：5625 黄花 2014年10月 MS-SQL Server大版内专家分月排行榜第二: 你把你的CTE1里面的SELECT 换成* 看看CTE1 你就懂了。为啥这个不能用上面的CHARINDEX。

0 2014-09-23 11:13:22

只看TA 引用举报 #2 得分 1

⋅今天希望大家帮个忙（不吹水） ⋅SQL Server 分区问题征集贴2 ⋅SQL Server 分区问题征集贴1 更多帖子 关注空间博客 發糞塗牆 本版专家分：214398 状元 2014年总版技术专家分年内排行榜第一 榜眼 2013年总版技术专家分年内排行榜第二 金牌 2014年8月总版技术专家分月排行榜第一 2014年7月总版技术专家分月排行榜第一 2014年6月总版技术专家分月排行榜第一 2014年5月总版技术专家分月排行榜第一 2014年4月总版技术专家分月排行榜第一 2014年3月总版技术专家分月排行榜第一 2014年1月总版技术专家分月排行榜第一 2013年12月总版技术专家分月排行榜第一 银牌 2013年10月总版技术专家分月排行榜第二 2012年11月总版技术专家分月排行榜第二: 反复测了一下，并且google了一下，只能说这是编译的过程中顺序并不是你写法那样，即使我再用一个cte包住同样出错，最后如果我把select code ,code2, charindex(code,code2) as c into #t from cte1
然后再
select * from #t where c>0就不抱错了，因为不需要重新计算charindex(code,code2)

0 2014-09-23 11:16:32

只看TA 引用举报 #3 得分 10

⋅今天希望大家帮个忙（不吹水） ⋅SQL Server 分区问题征集贴2 ⋅SQL Server 分区问题征集贴1 更多帖子 关注空间博客 發糞塗牆 本版专家分：214398 状元 2014年总版技术专家分年内排行榜第一 榜眼 2013年总版技术专家分年内排行榜第二 金牌 2014年8月总版技术专家分月排行榜第一 2014年7月总版技术专家分月排行榜第一 2014年6月总版技术专家分月排行榜第一 2014年5月总版技术专家分月排行榜第一 2014年4月总版技术专家分月排行榜第一 2014年3月总版技术专家分月排行榜第一 2014年1月总版技术专家分月排行榜第一 2013年12月总版技术专家分月排行榜第一 银牌 2013年10月总版技术专家分月排行榜第二 2012年11月总版技术专家分月排行榜第二: 另外只有charindex(code,code2)=0才报错，=1不抱错

0 2014-09-23 11:16:50

只看TA 引用举报 #4 得分 10

⋅Jquery checkbox 选择问题. ⋅[80]更新语句 ⋅【100分秒结】cte 里面的一个错误帮修正 更多帖子 关注空间博客 霜寒月冷 本版专家分：3772: 引用 2 楼 alimake 的回复:
你把你的CTE1里面的SELECT 换成* 看看CTE1 你就懂了。为啥这个不能用上面的CHARINDEX。

感谢你的回复，不过你解释了一堆貌似没什么用。换成*查询又如何，后面加条件还是会...

0 2014-09-23 11:18:31

只看TA 引用举报 #5 得分 0

⋅SQL 2000 配置邮件服务器 ⋅数据库表数据和EXCEL之间的转换 ⋅SQL 2000 作业 DTS 更多帖子 关注空间博客 xiaodongni 本版专家分：5625 黄花 2014年10月 MS-SQL Server大版内专家分月排行榜第二: 引用 5 楼 chz415767975 的回复:
Quote: 引用 2 楼 alimake 的回复:

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

感谢你的回复，不过你解释了一堆貌似没什么用。换成*查询又如何，后面加条件还是会...

不好意思我看错了。你这个报错和我的一样。但是写法不一样。
你先把最后的CTE1结果插入到一个表里面。然后再从这个里面进行SELECT就不会报错。
所以我觉得原因应该是CTE不会提高性能。只能方便阅读。
所以 SUBSTRING(','+t.[code],s.number,1) = ','  这个条件和CHARINDEX(code,code2)>0
没有先后问题。

0 2014-09-23 11:26:00

只看TA 引用举报 #6    得分 1

⋅SQL 2000 配置邮件服务器 ⋅数据库表数据和EXCEL之间的转换 ⋅SQL 2000 作业 DTS 更多帖子 关注空间博客 xiaodongni 本版专家分：5625 黄花 2014年10月 MS-SQL Server大版内专家分月排行榜第二: 但是这个报错内容还是让我不理解。看这个报错。应该是SUBSTRING发生溢出啊。为啥不加charindex
不报错。加了就溢出。

0 2014-09-23 11:27:17

只看TA 引用举报 #7 得分 1

⋅Jquery checkbox 选择问题. ⋅[80]更新语句 ⋅【100分秒结】cte 里面的一个错误帮修正 更多帖子 关注空间博客 霜寒月冷 本版专家分：3772: 引用 3 楼 DBA_Huangzj 的回复:
反复测了一下，并且google了一下，只能说这是编译的过程中顺序并不是你写法那样，即使我再用一个cte包住同样出错，最后如果我把select code ,code2, charindex(code,code2) as c into #t from cte1
然后再
select * from #t where c>0就不抱错了，因为不需要重新计算charindex(code,code2)

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

0 2014-09-23 11:29:45

只看TA 引用举报 #8 得分 0

⋅今天希望大家帮个忙（不吹水） ⋅SQL Server 分区问题征集贴2 ⋅SQL Server 分区问题征集贴1 更多帖子 关注空间博客 發糞塗牆 本版专家分：214398 状元 2014年总版技术专家分年内排行榜第一 榜眼 2013年总版技术专家分年内排行榜第二 金牌 2014年8月总版技术专家分月排行榜第一 2014年7月总版技术专家分月排行榜第一 2014年6月总版技术专家分月排行榜第一 2014年5月总版技术专家分月排行榜第一 2014年4月总版技术专家分月排行榜第一 2014年3月总版技术专家分月排行榜第一 2014年1月总版技术专家分月排行榜第一 2013年12月总版技术专家分月排行榜第一 银牌 2013年10月总版技术专家分月排行榜第二 2012年11月总版技术专家分月排行榜第二: 最近看《防御式编程》的书，里面很多地方说道不要“以为where条件会按照你的写法的顺序来运行”（大概这个意思），如果你真的要按顺序，常规做法是用case when来规定顺序，某些特殊情况下可以借助索引来把处理的优先级提高

0 2014-09-23 11:33:14

只看TA 引用举报 #9 得分 1

⋅好久没来，散分~~ ⋅近半年没上论坛了，散分~~ ⋅五一，嗨皮去吧 更多帖子 关注空间博客 还在加载中灬 本版专家分：52320 铜牌 2015年5月总版技术专家分月排行榜第三 2015年4月总版技术专家分月排行榜第三 2014年12月总版技术专家分月排行榜第三 红花 2015年6月 MS-SQL Server大版内专家分月排行榜第一 2015年5月 MS-SQL Server大版内专家分月排行榜第一 2015年4月 MS-SQL Server大版内专家分月排行榜第一 2015年3月 MS-SQL Server大版内专家分月排行榜第一 2015年2月 MS-SQL Server大版内专家分月排行榜第一 2015年1月 MS-SQL Server大版内专家分月排行榜第一 2014年12月 MS-SQL Server大版内专家分月排行榜第一 2014年11月 MS-SQL Server大版内专家分月排行榜第一 2014年10月 MS-SQL Server大版内专家分月排行榜第一: 研究了下，好奇葩，查了一些资料，都没有结果，关注一下

0 2014-09-23 13:33:07

只看TA 引用举报 #10 得分 1

⋅ 不购买Exadata一体机，ORACLE 12C中是否支持列式存储功能

⋅【2016论坛之星】投我一票

⋅【2016论坛之星】投票

更多帖子

关注空间博客

不想长大啊

本版专家分：84129

版主

金牌 2014年2月总版技术专家分月排行榜第一

银牌 2014年1月总版技术专家分月排行榜第二
2013年12月总版技术专家分月排行榜第二

优秀版主 2016年10月优秀小版主

微软MVP 2014年4月荣获微软MVP称号

引用 8 楼 chz415767975 的回复:

Quote: 引用 3 楼 DBA_Huangzj 的回复:

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

版主说的有点道理，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 函数的长度参数无效。



*/

0 2014-09-23 13:44:57

只看TA 引用举报 #11 得分 73

⋅ 不购买Exadata一体机，ORACLE 12C中是否支持列式存储功能

⋅【2016论坛之星】投我一票

⋅【2016论坛之星】投票

更多帖子

关注空间博客

不想长大啊

本版专家分：84129

版主

金牌 2014年2月总版技术专家分月排行榜第一

银牌 2014年1月总版技术专家分月排行榜第二
2013年12月总版技术专家分月排行榜第二

优秀版主 2016年10月优秀小版主

微软MVP 2014年4月荣获微软MVP称号: 出现这个问题，我觉得说明实际在执行的时候，并没有严格按照语句的顺序来执行，也就是会把最外层的where查询条件，直接放到了内部去了。

不是在内存结果计算出来后，再过滤数据，而是在计算过程中，就过滤了数据，才导致这种错误的

0 2014-09-23 13:48:45

只看TA 引用举报 #12 得分 0

⋅ 不购买Exadata一体机，ORACLE 12C中是否支持列式存储功能

⋅【2016论坛之星】投我一票

⋅【2016论坛之星】投票

更多帖子

关注空间博客

不想长大啊

本版专家分：84129

版主

金牌 2014年2月总版技术专家分月排行榜第一

银牌 2014年1月总版技术专家分月排行榜第二
2013年12月总版技术专家分月排行榜第二

优秀版主 2016年10月优秀小版主

微软MVP 2014年4月荣获微软MVP称号: 如果先把结果集放到临时表，然后再去查询临时表，那么就不会有这个问题了

0 2014-09-23 13:52:07

只看TA 引用举报 #13 得分 0

⋅Jquery checkbox 选择问题. ⋅[80]更新语句 ⋅【100分秒结】cte 里面的一个错误帮修正 更多帖子 关注空间博客 霜寒月冷 本版专家分：3772: 引用 12 楼 yupeigu 的回复:
出现这个问题，我觉得说明实际在执行的时候，并没有严格按照语句的顺序来执行，也就是会把最外层的where查询条件，直接放到了内部去了。

不是在内存结果计算出来后，再过滤数据，而是在计算过程中，就过滤了数据，才导致这种错误的
说的在理

0 2014-09-23 14:30:46

只看TA 引用举报 #14 得分 0

⋅请各位大神帮忙看看，结果不一样 更多帖子 关注空间博客 reenjie 本版专家分：4870: 在递归 CTE 中使用分析函数可能会出现问题，具体请参考下列连接第k点。不知道是否有用。
http://msdn.microsoft.com/zh-cn/library/ms175972.aspx

0 2014-09-23 14:58:27

只看TA 引用举报 #15 得分 1

【100分秒结】cte 里面 的一个错误帮修正 [问题点数：100分，结帖人chz415767975]

【100分秒结】cte 里面的一个错误帮修正 [问题点数：100分，结帖人chz415767975]