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

霜寒月冷 2014-09-23 10:40:34
刚刚在看帖子帮别人解决问题的时候,发现这么一个奇怪的错误,具体看代码最后的那一条语句!
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 函数的长度参数无效。
...全文
298 16 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
16 条回复
切换为时间正序
请发表友善的回复…
发表回复
發糞塗牆 2014-09-23
  • 打赏
  • 举报
回复
最近看《防御式编程》的书,里面很多地方说道不要“以为where条件会按照你的写法的顺序来运行”(大概这个意思),如果你真的要按顺序,常规做法是用case when来规定顺序,某些特殊情况下可以借助索引来把处理的优先级提高
霜寒月冷 2014-09-23
  • 打赏
  • 举报
回复
引用 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里面的执行顺序真是令人费解,我在看看其他的人的意见,再结贴!
xiaodongni 2014-09-23
  • 打赏
  • 举报
回复
但是这个报错内容还是让我不理解。看这个报错。应该是SUBSTRING发生溢出啊。为啥不加charindex 不报错。加了就溢出。
xiaodongni 2014-09-23
  • 打赏
  • 举报
回复
引用 5 楼 chz415767975 的回复:
[quote=引用 2 楼 alimake 的回复:] 你把你的CTE1里面的SELECT 换成* 看看CTE1 你就懂了。为啥这个不能用上面的CHARINDEX。
感谢你的回复,不过你解释了一堆貌似没什么用。换成*查询又如何,后面加条件还是会... [/quote] 不好意思 我看错了。你这个报错和我的一样。但是写法不一样。 你先把最后的CTE1结果插入到一个表里面。然后再从这个里面进行SELECT就不会报错。 所以 我觉得原因应该是CTE不会提高性能。只能方便阅读。 所以 SUBSTRING(','+t.[code],s.number,1) = ',' 这个条件和CHARINDEX(code,code2)>0 没有先后问题。
霜寒月冷 2014-09-23
  • 打赏
  • 举报
回复
引用 2 楼 alimake 的回复:
你把你的CTE1里面的SELECT 换成* 看看CTE1 你就懂了。为啥这个不能用上面的CHARINDEX。
感谢你的回复,不过你解释了一堆貌似没什么用。换成*查询又如何,后面加条件还是会...
發糞塗牆 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
  • 打赏
  • 举报
回复
引用 12 楼 yupeigu 的回复:
出现这个问题,我觉得说明实际在执行的时候,并没有严格按照 语句的顺序来执行,也就是会把最外层的where查询条件,直接放到了内部去了。 不是在内存结果计算出来后,再过滤数据,而是在计算过程中,就过滤了数据,才导致这种错误的
说的在理
LongRui888 2014-09-23
  • 打赏
  • 举报
回复
如果先把结果集放到临时表,然后再去查询临时表,那么就不会有这个问题了
LongRui888 2014-09-23
  • 打赏
  • 举报
回复
出现这个问题,我觉得说明实际在执行的时候,并没有严格按照 语句的顺序来执行,也就是会把最外层的where查询条件,直接放到了内部去了。 不是在内存结果计算出来后,再过滤数据,而是在计算过程中,就过滤了数据,才导致这种错误的
LongRui888 2014-09-23
  • 打赏
  • 举报
回复
引用 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里面的执行顺序真是令人费解,我在看看其他的人的意见,再结贴![/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
  • 打赏
  • 举报
回复
研究了下,好奇葩,查了一些资料,都没有结果,关注一下

34,838

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧