--1) declare @tag varchar(10) select @tag=null select * from t_analyze_h where tag like isnull(@tag,'%')select * from t_analyze_h where tag like '%' --一样吗?--因为 @tag=null ,所以结果一样 --如果 @tab不会null,则结果不一样
--2) declare @tag char(10) select @tag=null select * from t_analyze_h where tag like isnull(@tag,'%') --和declare @tag varchar(10) select @tag=null select * from t_analyze_h where tag like isnull(@tag,'%')--有什么区别?--有区别,第一个会在%后面补空格,第二个不会 --说明这个问题的示例: declare @a char(10),@b varchar(10) select @a=null,@b=null select case when 'a' like isnull(@a,'%') then 1 else 0 end ,case when 'a' like isnull(@b,'%') then 1 else 0 end--结果: ----------- ----------- 0 1(所影响的行数为 1 行)
--测试... ------------------------------------------ --1) declare @tag varchar(10) set @tag='null'print('1,select * from t_analyze_h where tag like isnull('+@tag+',''%'')')print('2,select * from t_analyze_h where tag like ''%''') --2) declare @tag1 char(10) select @tag1='null' print('3,select * from t_analyze_h where tag like isnull('+@tag1+',''%'')')declare @tag2 varchar(10) select @tag2='null' print('4,select * from t_analyze_h where tag like isnull('+@tag2+',''%'')') --结果... ------------------------------------------ 1,select * from t_analyze_h where tag like isnull(null,'%') 2,select * from t_analyze_h where tag like '%' 3,select * from t_analyze_h where tag like isnull(null ,'%') 4,select * from t_analyze_h where tag like isnull(null,'%')
1) 当变量不为null时,两句显然不一样。2)当后一种的变量字串长度不为10时,结果也不一样。
declare @tag varchar(10)
select @tag=null
select * from t_analyze_h
where tag like isnull(@tag,'%')select * from t_analyze_h
where tag like '%' --一样吗?--因为 @tag=null ,所以结果一样
--如果 @tab不会null,则结果不一样
declare @tag char(10)
select @tag=null
select * from t_analyze_h
where tag like isnull(@tag,'%')
--和declare @tag varchar(10)
select @tag=null
select * from t_analyze_h
where tag like isnull(@tag,'%')--有什么区别?--有区别,第一个会在%后面补空格,第二个不会
--说明这个问题的示例:
declare @a char(10),@b varchar(10)
select @a=null,@b=null
select case when 'a' like isnull(@a,'%') then 1 else 0 end
,case when 'a' like isnull(@b,'%') then 1 else 0 end--结果:
----------- -----------
0 1(所影响的行数为 1 行)
在执行查询时,查询出的结果数量不一致.
------------------------------------------
--1)
declare @tag varchar(10)
set @tag='null'print('1,select * from t_analyze_h
where tag like isnull('+@tag+',''%'')')print('2,select * from t_analyze_h
where tag like ''%''')
--2)
declare @tag1 char(10)
select @tag1='null'
print('3,select * from t_analyze_h
where tag like isnull('+@tag1+',''%'')')declare @tag2 varchar(10)
select @tag2='null'
print('4,select * from t_analyze_h
where tag like isnull('+@tag2+',''%'')')
--结果...
------------------------------------------
1,select * from t_analyze_h
where tag like isnull(null,'%')
2,select * from t_analyze_h
where tag like '%'
3,select * from t_analyze_h
where tag like isnull(null ,'%')
4,select * from t_analyze_h
where tag like isnull(null,'%')