经常可以遇到这种情况
分类:博客热点

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

图片 1图片 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_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)_________________________________上面两种方法,哪种方法效率高?

图片 3图片 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 

(7)EXCEPT和Not in的区别?

 

(8)INTERSECT和UNION的区别?下面是邹老大的回答:

6、简单静态游标

(1)一次插入多条数据时:

图片 5图片 6代码

第1种好一些, 但也得有个, 因为第1种的union all是做为一个语句整体, 查询优化器会尝试做优化, 同时, 也要先算出这个结果再插入的.

DECLARE product_cursor CURSOR STATIC FOR 
SELECT cName FROM Product 
OPEN product_cursor
FETCH NEXT FROM product_cursor INTO @product
WHILE @@FETCH_STATUS = 0
BEGIN
 SELECT @message = ' ' + @product
 PRINT @message
 FETCH NEXT FROM product_cursor INTO @product
END
CLOSE product_cursor
DEALLOCATE product_cursor

  1. 如果是单个赋值, 没有什么好比较的话.不过, 如果是为多个变量赋值, 我测试过, SELECT 一次性赋值, 比用SET 逐个赋值效率好.

  2. SET ROWCOUNT和TOP 是一样的, 包括执行的计划等都是一样的

  3. 这个一般是exists快, 当然, 具体还要看你后面的子查询的条件, 是否会引用外层查询中的对象的列.exists检查到有值就返回, 而且不返回结果集, count需要统计出所有满足条件的, 再返回一个结果集, 所以一般情况下exists快.

  4. 应该是一样的

  5. 基本上是一样的

  6. except会去重复, not in 不会(除非你在select中显式指定)except用于比较的列是所有列, 除非写子查询限制列, not in 没有这种情况8. intersect是两个查询都有的非重复值(交集), union是两个查询结果的所有不重复值(并集)

 

7、要求是取得每个ID对应postId的前三条

图片 7图片 8代码

CREATE TABLE [tb] (Id INT ,postId INT )
INSERT INTO [tb] 
SELECT 2788 , 45753530 UNION ALL 
SELECT 6417 , 46862065 UNION ALL 
SELECT 61773 , 47407456 UNION ALL
SELECT 61773 , 47436468 UNION ALL
SELECT 61773 , 47448259 UNION ALL 
SELECT 61773 , 47474393 UNION ALL 
SELECT 83604 , 41671947 UNION ALL 
SELECT 83604 , 45858681 UNION ALL
select id, postid from ( select * ,cid = row_number() over (partition by id order by id) from tb ) as t
where t.cid <= 3

 

8、实现编号自动增长

图片 9图片 10代码

--下面的代码生成长度为8的编号,编号以BH开头,其余6位为流水号。
-- 得到新编号的函数 
CREATE FUNCTION f_NextBH()
RETURNS char ( 8 )
AS 
BEGIN 
RETURN (SELECT 'BH' +RIGHT(1000001 + ISNULL(RIGHT(MAX(BH),6),0),6) FROM tb WITH(XLOCK,PAGLOCK))
END 
GO 
-- 在表中应用函数 
CREATE TABLE tb(
BH char (8) PRIMARY KEY DEFAULT dbo.f_NextBH(),col int)
-- 插入资料 
BEGIN TRAN 
INSERT tb(col) VALUES ( 1 )
INSERT tb(col) VALUES ( 2 )
INSERT tb(col) VALUES ( 4 )
INSERT tb(BH,col) VALUES (dbo.f_NextBH(), 14 )
COMMIT TRAN 

9、表Age_AssignedNum中的字段CallRecordId,其值有三种:K、N、null,
   按PlanId分组统计出这三种值各有多少行

图片 11图片 12代码

SELECT PlanId,sK=count(CASE CallRecordId WHEN 'K' then CallRecordId end),
    sN=count(CASE CallRecordId WHEN 'N' then CallRecordId end),
    sNull=count( CASE isnull(CallRecordId,'') WHEN '' then '1' end)
FROM Age_AssignedNum GROUP BY PlanId 

 

 

本文由威尼斯在线注册平台发布于博客热点,转载请注明出处:经常可以遇到这种情况

上一篇:PHP的片段系统函数 下一篇:没有了
猜你喜欢
热门排行
精彩图文