本文转载自微信公众号「DBA闲思杂想录」,作者潇湘隐者 。转载本文请联系DBA闲思杂想录公众号。
在SQL Server的查询条件中,能否在IN里面使用变量呢?如果可以的话,有没有需要注意的地方或一些限制呢?在回答这个问题前,我们先来看看这个例子:
- IF EXISTS (SELECT 1 FROM sys.objects WHERE name='TEST' AND type='U')
- BEGIN
- DROP TABLE TEST;
- END
- GO
- CREATE TABLE TEST ( ID INT, NAME VARCHAR(16) );
- GO
- INSERT INTO dbo.TEST
- SELECT 1, 'a' UNION ALL
- SELECT 2, 'b' UNION ALL
- SELECT 3, 'c' UNION ALL
- SELECT 4, 'a,b'UNION ALL
- SELECT 5, '''b'',''c''' UNION ALL
- SELECT 6, '''b';
- GO
如下所示,如果查询条件里面,变量只有一个值,此时SQL是正常的。
- DECLARE @name VARCHAR(16);
- SET @name='a';
- SELECT * FROM TEST WHERE name IN (@name);
- GO
- DECLARE @name VARCHAR(16);
- SET @name='a,b';
- SELECT * FROM TEST WHERE name IN (@name);
- GO
如果我们想在查询条件IN里面输入多个值呢?假如有这样的一个需求,一个变量里面包含b和c的值,现在用'b|c’作为条件传入,对其进行拆分为变量'b'和'c', 想查出name=b 和name=c的记录,如下截图所示,SQL其实并没有按你所“设想/预想”的查出对应记录,而是将ID=5的记录查出来了
- DECLARE @name1 VARCHAR(16);
- DECLARE @name2 VARCHAR(16);
- SET @name1='b|c';
- SET @name2=REPLACE(@name1,'|',''',''')
- SELECT @name2
- SELECT * FROM TEST WHERE name IN (('''' + @name2 + ''''));
下面这个SQL也是同样的结果。
- DECLARE @name1 VARCHAR(16);
- DECLARE @name2 VARCHAR(16);
- SET @name1='b|c';
- SET @name2='''' + REPLACE(@name1,'|',''',''') +''''
- SELECT @name2
- SELECT * FROM TEST WHERE name IN (@name2 );
为什么出现了这样的结果呢?查了大量的官方文档,没有看到关于这个问题的介绍和解释。如果一定要解释上面现象的情况的话,那么是因为SELECT * FROM TEST WHERE name IN (@name2 ); 其实转化为了SELECT * FROM TEST WHERE name =@name2; 也就是说,上面SQL并不会按你所“设想”的逻辑运算。而是做了一个转换,为什么说是这样的一个转换呢?当然这也是一个猜想,上面构造的例子也是为了侧面验证这个猜想,另外,上面两个SQL实际执行计划的参数列表(Parameter List)也侧面印证了这个猜想(如下截图所示)。如果执行计划解析成我们想要的结果,那么Parameter List应该是'b' 和‘c'
解决方案:
1:使用动态SQL
使用动态SQL解决问题,似乎没啥好说的,如下例子所示:
- DECLARE @sql_cmd NVARCHAR(max);
- DECLARE @name VARCHAR(16);
- SET @name='b|c';
- SET @sql_cmd='SELECT * FROM TEST WHERE name IN (''' + REPLACE(@name,'|',''',''') +''');'
- EXEC sp_executesql @sql_cmd;
2:使用临时表或表变量
以这个例子来说,就是将字符串拆分,放入临时表或表变量,然后关联表也好,在IN里面使用子查询也OK。
3:借助STRING_SPLIT()
- DECLARE @name VARCHAR(16);
- SET @name='b|c';
- SELECT *FROM test WHERE name IN (SELECT value FROM STRING_SPLIT(@name, '|'))
注意:STRING_SPLIT函数只有较高版本才支持,SQL Server 2017或SQL Server 2016部分版本支持。
4:借助XML函数来解决问题
- DECLARE @name VARCHAR(16);
- DECLARE @xml_para XML;
- SET @name = 'b|c';
- SET @xml_para = CAST(( '<A>' + REPLACE(@name, '|', '</A><A>') + '</A>' ) AS XML);
- SELECT *
- FROM dbo.TEST
- WHERE NAME IN ( SELECT A.value('.', 'varchar(max)') AS [Column]
- FROM @xml_para.nodes('A') AS FN ( A ) );
大家好,我是狂聊君。 今天来聊一聊 Mysql 缓存池原理。 提纲附上,话不多说,直...
前言 项目开发中不管是前台还是后台都会遇到烦人的null,数据库表中字段允许空值...
本文实例讲述了AJAX+Servlet实现的数据处理显示功能。分享给大家供大家参考,具...
本文转载自微信公众号「SH的全栈笔记」,作者SH。转载本文请联系SH的全栈笔记公...
在Flash Player 10.1及以上版本中,adobe新增了全局错误处理程序UncaughtErrorEv...
来源:DeepenStudy 漏洞文件:js.asp % Dimoblog setoblog=newclass_sys oblog.a...
idea官方推送了2020.2.4版本的更新,那么大家最关心的问题来了,之前激活idea202...
问题:我们在做flex的开发中,如果用到别人搭建好的框架,而别人的server名称往...
CKeditor,以前叫FCKeditor,已经使用过好多年了,功能自然没的说。最近升级到3....
本文转载自微信公众号「SQL数据库」,作者丶平凡世界 。转载本文请联系开发公众...