---这篇文章仅是对自己的一个提醒 写的比较凌乱
declare @s datetime
declare @e datetime
declare @n varchar(10)
set @s = convert(datetime,'2006-12-01')
set @e = convert(datetime,'2007-09-09')
set @n = 'm'
select * from sales.orders where orderdate between @s and @e and shipcity like @n+'%'
shipcity like +''+@n+'%'
shipcity like ''+@n+'%'
shipcity like +@n+'%'
这四种like的写法在本例中都是正确的。
在sqlserver中+除了算数运算外,就是用作字符串相加的(脑子中要有字符串相加的概念)。
解析字符串相加的过程如下:
because @n='m'
so ' '+@n+'%'=' '+'m'+'%'='m%'
如declare @ss nvarchar(50);
set @ss='I am'+' andy';
select @ss;
print I am andy--------I am andy是字符串 相当于'I am andy'
like后面出现的东西必须是字符串 用加号可以拼接多个字符串
like的原型就是 name like 'm%'
变量的形式: shipcity like ''+@n+'%'
实际的形式应该是: shipcity like 'n%'
那''+@n+'%' 怎么解析成了'n%'呢 这个问题困扰很久了
解惑 because @n='m'
so ' '+@n+'%'=' '+'m'+'%'='m%'
问题解答完毕。
再举一例对动态sql中的变量拼接进行一下讨论:
declare @s datetime;
declare @e datetime;
declare @n varchar(10);
declare @sql nvarchar(1000);
set @s = convert(datetime,'2006-12-01')
set @e = convert(datetime,'2007-09-09')
set @n = 'm'
set @sql = 'select * from sales.orders where orderdate between '' '----------最前好最后一个单引号是一对表示字符串 这两个‘''’单引号表示一个单引号(单引号转义),包含转义后的一个单引号是betwwen的语法要求(如:startdte between '2009-12-01' and '2009-12-09')
+convert(varchar,@s,120)---------这是个字符串,相当于'2006-12-01'
+' '' and '' '
+convert(varchar,@e,120)
+' '' and shipcity like ''% '
+@n
+' %'' '
exec(@sql)
print @sql;
print @sql打印出的内容如下:
select * from sales.orders
where orderdate between '2006-12-01 00:00:00' and '2007-09-09 00:00:00'
and shipcity like '%m%'