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''')
posted on 2011-03-18 14:55 jianshaohui 阅读(417) 评论(2) 编辑 收藏 举报