记录下最近项目中常用到的SQL语句

1  实现对字符串的Spilt功能。 比如查出“I have a dream!”总共有几个单词，需要以' '分割，然后再求出总数。

ALTER function [dbo].[fc_SpiltString]
(
@str varchar(1024), --要分割的字符串
@split varchar(10) --分隔符号
)
returns int
as
begin
declare @location int
declare @start int
declare @length int

set @str=ltrim(rtrim(@str))
set @location=charindex(@split,@str)
set @length=1
while @location<>0
begin
   set @start=@location+1
   set @location=charindex(@split,@str,@start)
   set @length=@length+1
end
return @length
end
GO

2 表连接进行增删改

--INSERT 
INSERT INTO [TABLE1]([ID],[NAME],[ClassName])
 SELECT S.ID,S.NAME,C.[ClassName] FROM
[STUDENT] AS S INNER JOIN [CLASS] AS C 
ON S.CID=C.ID

--UPDATE
UPDATE S SET S.NAME='XXX' FROM [STUDENT] AS S
 INNER JOIN [CLASS] AS  C
 ON S.CID=C.ID WHERE ..

 --DELETE
 DELETE S FROM [STUDENT] AS S
 INNER JOIN [CLASS] AS  C
 ON S.CID=C.ID WHERE ..

3 对临时表的基本操作（insert into #temp和select * into #temp ）具体实现语句不写，只写怎么判断。

IF (object_id('tempdb..#Temp') is  null  )
BEGIN
  CREATE TABLE #Temp(
    Name nvarchar(50) not null,
  
    )
END
ELSE
 BEGIN
  TRUNCATE TABLE #Temp
 END

 4 开启sql执行统计功能

set statistics io on 
set statistics time on
set statistics profile on

 5 except 代替not in 

 select distinct [name] from [T] except select  [name] from [T2]
 select [name] [name]from [T]  where TgtCulture  not  in (select  [name] from [T2])

性能对比

T2:Scan count 1, logical reads 3009 
T:Scan count 1, logical reads 2968,

 SQL Server Execution Times:
   CPU time = 124 ms,  elapsed time = 127 ms.

T2:Scan count 7, logical reads 152215
T:Scan count 3, logical reads 3256
 SQL Server Execution Times:
   CPU time = 311 ms,  elapsed time = 183 ms.

 

 关于排序聚合函数：

 row_number()  无重复不可并列的排序；

  Rank()  相等的数据并列排序；

 over (partition by xx) 按照XX 分组  效果等同于 Group by ;

下面的句子是  按照工资从大到小并且可以并列排名 给每个班级进行排名

select  saray, name,groupid,rank() over(partition by groupid order by saray desc) rankId from [UserSarary]

 

表变量（把表当作参数执行方法或者存储过程）

--建立自定义表类型
create type MyTableV as table (ID int null)
--建立存储过程
create PROCEDURE [dbo].[temptest](
    @Source as MyTableV readonly,
    @table as MyTableV readonly
    )
    as 
    begin
    select  *  from @source;
    select  *  from @table
    end 
--声明表变量 @source 第一个参数
DECLARE @source TABLE(ID INT)
declare @source MyTableV 
insert into @source values(3)
--声明表变量 @table 第二个参数
DECLARE @table TABLE(ID INT)
declare @table MyTableV 
insert into @table values(31)
--执行存储过程
exec [temptest] @source,@table