菜单

方法汇总,MSSQL各种写法的效率问题

2020年4月16日 - 4166am金沙下载

核心提示:经常可以遇到这种情况:用不同的SQL写法可以达到同样的目的

4166am金沙下载 14166am金沙下载 2代码

sqlserver字符串拆分(split)方法汇总

 转载

–方法0:动态SQL法 declare @s varchar(100),@sql varchar(1000)
set @s=’1,2,3,4,5,6,7,8,9,10′
set @sql=’select col=”’+ replace(@s,’,’,”’ union all select
”’)+””
PRINT @sql
exec (@sql)

 

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[f_splitSTR]’) and xtype in (N’FN’, N’IF’,
N’TF’))
drop function [dbo].[f_splitSTR]
GO
–方法1:循环截取法 CREATE FUNCTION f_splitSTR(
@s   varchar(8000),   –待分拆的字符串
@split varchar(10)     –数据分隔符
)RETURNS @re TABLE(col varchar(100))
AS
BEGIN
 DECLARE @splitlen int
 SET @splitlen=LEN(@split+’a’)-2
 WHILE CHARINDEX(@split,@s)>0
 BEGIN
  INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1))
  SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,”)
 END
 INSERT @re VALUES(@s)
 RETURN
END
GO

 

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[f_splitSTR]’) and xtype in (N’FN’, N’IF’,
N’TF’))
drop function [dbo].[f_4166am金沙下载,splitSTR]
GO
–方法2:使用临时性分拆辅助表法
CREATE FUNCTION f_splitSTR(
@s   varchar(8000),  –待分拆的字符串
@split varchar(10)     –数据分隔符
)RETURNS @re TABLE(col varchar(100))
AS
BEGIN
 –创建分拆处理的辅助表(用户定义函数中只能操作表变量)
 DECLARE @t TABLE(ID int IDENTITY,b bit)
 INSERT @t(b) SELECT TOP 8000 0 FROM syscolumns a,syscolumns b

 INSERT @re SELECT SUBSTRING(@s,ID,CHARINDEX(@split,@s+@split,ID)-ID)
 FROM @t
 WHERE ID<=LEN(@s+’a’)
  AND CHARINDEX(@split,@split+@s,ID)=ID
 RETURN
END
GO

 

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[f_splitSTR]’) and xtype in (N’FN’, N’IF’,
N’TF’))
drop function [dbo].[f_splitSTR]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tb_splitSTR]’) and
objectproperty(id,N’IsUserTable’)=1)
drop table [dbo].[tb_splitSTR]
GO
–方法3:使用永久性分拆辅助表法 –字符串分拆辅助表
SELECT TOP 8000 ID=IDENTITY(int,1,1) INTO dbo.tb_splitSTR
FROM syscolumns a,syscolumns b
GO
–字符串分拆处理函数
CREATE FUNCTION f_splitSTR(
@s     varchar(8000),  –待分拆的字符串
@split  varchar(10)     –数据分隔符
)RETURNS TABLE
AS
RETURN(
 SELECT col=CAST(SUBSTRING(@s,ID,CHARINDEX(@split,@s+@split,ID)-ID) as
varchar(100))
 FROM tb_splitSTR
 WHERE ID<=LEN(@s+’a’)
  AND CHARINDEX(@split,@split+@s,ID)=ID)
GO

 

–方法4:利用sql server2005的OUTER APPLY

CREATE FUNCTION [dbo].[ufn_SplitStringToTable]
(
  @str VARCHAR(MAX) ,
  @split VARCHAR(10)
)
RETURNS TABLE
    AS
RETURN
    ( SELECT    B.id
      FROM      ( SELECT    [value] = CONVERT(XML , ‘<v>’ +
REPLACE(@str , @split , ‘</v><v>’)
                            + ‘</v>’)
                ) A
      OUTER APPLY ( SELECT  id = N.v.value(‘.’ , ‘varchar(100)’)
                    FROM    A.[value].nodes(‘/v’) N ( v )
                  ) B
    )

 

备注说明:

方法4必须在sql server2005下才可以运行

经常可以遇到这种情况:用不同的SQL写法可以达到同样的目的。爱钻牛角尖的人就想搞明白,诚然结果一样,但到底孰优孰劣?下面是我列出的一些,请兄弟们赐教。也请兄弟们将你们遇到的类似问题一并列出。

SQLServer 中的回车符与换行符
制表符:CHAR(9)  换行符:CHAR(10)  回车符:CHAR(13)
1、将 varchar 值作为条件,查询Where Id in 列表的值 
declare @a varchar ( 100 )
set @a = ‘56,58’   
— 正确的: 
select * from tb where charindex ( ‘,’ + ltrim (id) + ‘,’ , ‘,’ + @a + ‘,’ ) > 0 
— 错误的: 
select * from tb where id in + @a 

(1)一次插入多条数据时:CREATE TABLE tb(ID int, 名称 NVARCHAR(30), 备注
NVARCHAR(1000))INSERT tbSELECT 1,DDD,1UNIONALLSELECT
1,5100,DUNIONALLSELECT 1,5200,E

2、找出与某id相近的四条记录
Create table tb(id int ,cName char ( 10 ))
DECLARE @ID INT         
SET @ID = 7 
SELECT * FROM TB A WHERE id in 
( SELECT TOP 4 id FROM TB ORDER BY ABS (id – @id ))
ORDER BY ID 

也可以这样:CREATE TABLE tb1(ID int, 名称 NVARCHAR(30), 备注
NVARCHAR(1000))INSERT TB1 (ID,名称,备注)VALUES(1,DDD,1)INSERT TB1
(ID,名称,备注)VALUES(1,5100,D)INSERT TB1
(ID,名称,备注)VALUES(1,5200,E)_________________________________上面两种方法,哪种方法效率高?

3、按名称,规格分组,将单价数据合并成一行,并计算数量
Create table [tb] (tName varchar(4), [tSize] varchar (7), [ tPrice ] int , [ tQty ] int )
insert [ tb ] 
select ‘高瓦’ , ‘880*110’  , 22 , 1 union all select ‘高瓦’ , ‘880*110’ , 25 , 1 union all 
select ‘高瓦’ , ‘880*110’ , 22 , 1 union all select ‘高瓦’ , ‘880*120’ , 22 , 1 

(2)赋值时:SELECT @a=NaaSET
@a=Naa_________________________________上面两种方法,哪种方法效率高?

select   tName, tSize,
  tPrice = stuff (( select ‘,’ + ltrim (tPrice) from tb  
     where tName = t.tName and tSize = t.tSize FOR XML PATH(”)),1,1,”), sum(tQty)
as tQty
from   tb t group by   tName, tSize 
—结果—————————– 
tName   tSize     tPrice     tQty
高瓦    880*110    22,25,22  3
高瓦    880*120    22        1 

(3)取前几条数据时set ROWCOUNT 2 select * from tb order by fdselect Top
2 * from tb order by
fd_________________________________上面两种方法,哪种方法效率高?

4、根据出生日期,计算出准确的年龄 
SET @A = ‘2008-08-12’ 
DECLARE @A DATETIME 
SELECT 年龄 = 
case when datediff(day,dateadd(year,datediff(year,@A,getdate()),@A), getdate())>= 0 
      then datediff(year,@A,getdate()) else datediff(YY,@A,getdate())- 1 end 

(4)条件判断时where 0(select count(*) from tb where ……)where
exists(select * from tb where ……)
_________________________________上面两种方法,哪种方法效率高?

5、找出某目录列表中所有下级目录,包括自己

(5)NULLIF的使用—–同理它的反函数ISNULL的使用update tb set fd=case when
fd=1 then null else fd endupdate tb set
fd=nullif(fd,1)_________________________________上面两种方法,哪种方法效率高?

4166am金沙下载 34166am金沙下载 4代码

(6)从字符串中取子字符串时substring(abcdefg,1,3)left(abcderg,3)_________________________________上面两种方法,哪种方法效率高?

DECLARE @FolderList varchar ( 800 )
SET @FolderList = ‘1’ 
SET NOCOUNT ON 
    CREATE TABLE # Temp (FolderId int )    
    INSERT # Temp 
    SELECT FolderId FROM Doc_Folder
    WHERE CHARINDEX ( ‘,’ + LTRIM (FolderId) + ‘,’ , ‘,’ + @FolderList + ‘,’ ) > 0     
    WHILE @@Rowcount > 0 
    BEGIN 
        INSERT # Temp SELECT FolderId FROM Doc_Folder AS A WHERE 
            EXISTS ( SELECT 1 FROM # Temp AS B WHERE B. [ FolderId ] = A.ParentFolderId)
            AND NOT EXISTS(SELECT 1 FROM # Temp AS B WHERE B. [FolderId] = A.[FolderId])
    END 

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图