sql實現(xiàn)分段功能的函數(shù)
--簡單分段語句
declare @b varchar(20)
select @b='a-b-c-d-e'
--處理
declare @sql varchar(4000)
select @sql= 'select '''+replace(@b,'-',''' union all select ''') ''''
exec(@sql)
--實現(xiàn) split 分段功能的函數(shù)
create function f_split(@SourceSql varchar(8000),@StrSeprate varchar(10))
returns @temp table(a varchar(100))
as
begin
declare @i int
set @SourceSql=rtrim(ltrim(@SourceSql))
set @i=charindex(@StrSeprate,@SourceSql)
while @i>=1
begin
insert @temp values(left(@SourceSql,@i-1))
set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
set @i=charindex(@StrSeprate,@SourceSql)
end
if @SourceSql<>'\'
insert @temp values(@SourceSql)
return
end
--用法:
select * from dbo.f_split('A:B:C:D:E',':')
--輸出
a
1 A
2 B
3 C
4 D
5 E
--實現(xiàn)簡單模糊查找的過程
create procedure splitter
@str varchar(1024)
as
declare @s varchar(1024)
declare @i int
set @s='select message from T_QQMsgLog where 1=1'
set @i=1
--------------------------------------------------------------------------------------
去掉重復(fù)記錄，獲取重復(fù)記錄
--查詢一個表中有效去掉重復(fù)的記錄，UserID為自增長主鍵，RoleID為重復(fù)字段
select MIN(UserID) as UserID, RoleID from tmpTable group by RoleID
select RoleID from tmpTable group by RoleID HAVING (count(*) > 1)
select distinct * from tmpTable
--刪除重復(fù)記錄
select distinct * into #Tmp from tableName
drop table tableName
select * into tableName from #Tmp
drop table #Tmp