问题:存在查询条件会入参上千个的情况,用in 查询感觉会慢
所以想字符串转成table 用表关联会不会快一点...
字符串转table的SQL:
CREATE FUNCTION strToTable(@str VARCHAR(8000))
RETURNS @TableName TABLE
(str2table VARCHAR(50))
AS
BEGIN
SET @str=@str+','
DECLARE @insertStr VARCHAR(50)
DECLARE @newStr VARCHAR(8000)
SET @insertStr=LEFT(@str,CHARINDEX(',',@str)-1)
SET @newStr=STUFF(@str,1,CHARINDEX(',',@str),'')
INSERT @TableName VALUES(@insertStr)
WHILE(LEN(@newstr)>0)
BEGIN
SET @insertStr=LEFT(@newstr,CHARINDEX(',',@newstr)-1)
INSERT @TableName VALUES(@insertStr)
SET @newStr=STUFF(@newstr,1,CHARINDEX(',',@newstr),'')
END
RETURN
END
完成测试用:SELECT * FROM strToTable('1,2,31,12A012')