sql精点语句

1.查询语句的字符相加

select ','+isnull(bossnum,'') from dbo.atest for xml path('')

2.使用模式分割字符串

declare @s varchar(1000)
set @s=N'1AAAA2可以了嗎BBBBB3CCCC23698好的adadasd.,,'
declare @t table(ID int ,[name] varchar(100))
while patindex('%[0-9]%',@s)>0
  begin
     declare @ID varchar(10),@name varchar(100)
     if patindex('%[0-9]%',stuff(@s,1,patindex('%[^0-9]%',@s)-1,''))=0
        begin
          set @id=left(@s,patindex('%[^0-9]%',@s)-1)
          set @name=right(@s,len(@s)-patindex('%[^0-9]%',@s)+1)
          set @s=''
        end
     else
        begin
          set @id=left(@s,patindex('%[^0-9]%',@s)-1)
          set @s=stuff(@s,1,patindex('%[^0-9]%',@s)-1,'')
          set @name=left(@s,patindex('%[0-9]%',@s)-1)
          set @s=stuff(@s,1,patindex('%[0-9]%',@s)-1,'')
        end
     insert @t select @id,@name
     print @s
  end
select * from @t
/*
ID          name
----------- ----------------------------------------------------------------------------------------------------
1           AAAA
2           可以了嗎BBBBB
3           CCCC
23698       好的adadasd.,,

(4 個資料列受到影響)
*/

3.去重

select * from Module a
where  not exists ( select * from Module where ModuleID>a.ModuleID and a.ModuleName= ModuleName)

with test as (
select * ,row_number() over(partition by username order by uid) as rowid from mytest
)
DELETE FROM TEST WHERE ROWID>1

SELECT * fROM MYTEST

4、分割函数

/*

declare @str nvarchar(max)
set @str='a,b,c,d'
declare @tmpstr nvarchar(max),@split nvarchar(5),@val nvarchar(50),@index int
set @tmpstr=@str
set @split=','
set @index =  charindex(@split,@tmpstr)

declare @tab table(cal nvarchar(50))
while (@index>0)
 begin
  set @val = substring(@tmpstr,1,(@index-1))
  if @val<>''
   begin
    insert into @tab values(@val)
   end
  set @tmpstr = stuff(@tmpstr,1,@index,'')
  set @index= charindex(@split,@tmpstr,@index)
 end
if @tmpstr<>''
 insert into @tab values(@tmpstr)
select * from @tab

*/
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

create   function [dbo].[StrSplit](@c   varchar(2000),@split   varchar(2))  
  returns   @t   table(col   varchar(20))  
  as  
    begin  
    declare @tempstr varchar(50)
      while(charindex(@split,@c)<>0)  
        begin  
set @tempstr=substring(@c,1,charindex(@split,@c)-1);
if(@tempstr<>'')
          insert   @t(col)   values   (@tempstr)  
          set   @c   =   stuff(@c,1,charindex(@split,@c),'')  
        end  
if(@c<>'')
      insert   @t(col)   values   (@c)  
      return  
    end  

declare @str nvarchar(max)
set @str='22-1000:553-2000:2100-1000:22-10000:553-2000:2100-1000:22-500:553-2000:2100-1000:553-2000:22-1000:2100-1000:553-2000:22-500:2100-1000:553-2000:2100-1000:22-500'

SELECT  * from  dbo.StrSplit(@str,':') where col like '22-%'

5、收缩日志

declare @dbname sysname
set @dbname='SCP'
--1.清空日志
   exec('DUMP TRANSACTION ['+@dbname+'] WITH   NO_LOG')

--2.截断事务日志：
   exec('BACKUP LOG ['+@dbname+'] WITH NO_LOG')

  --3.收缩数据库文件(如果不压缩,数据库的文件不会减小
  exec('DBCC SHRINKDATABASE(['+@dbname+'])')

 --4.设置自动收缩
   exec('EXEC sp_dboption '''+@dbname+''',''autoshrink'',''TRUE''')