sql语句总结一

1、查找某一个部门的所有上级部门节点信息

ALTER function [dbo].[fnGetFatherGroup](@id varchar(50)) 
returns @t table(GroupID varchar(50),G_ParentID varchar(50),G_Level int,G_IsCompany int) 
as 
begin 
insert into @t select GroupID,G_ParentID,G_Level,G_IsCompany from sys_Group where GroupID = @id 
select @id = G_ParentID from sys_Group where GroupID = @id 
while @@rowcount > 0 
begin 
insert into @t select GroupID,G_ParentID,G_Level,G_IsCompany from sys_Group where GroupID = @id 
select @id = G_ParentID from sys_Group where GroupID = @id 
end 
return 
end 

 

2、查找表中某一列最大值的信息

select a.GroupID from fnGetFatherGroup(56) a where G_Level = ( select max(G_Level) from fnGetFatherGroup(56) where G_IsCompany = 1 ) 

3、函数用于查找字符串中重复出现的字符

ALTER FUNCTION [dbo].[fnGetDistinctStr](@S NVARCHAR(100),@SPLIT VARCHAR(50))
RETURNS VARCHAR(100)
AS
BEGIN
IF @S IS NULL RETURN(NULL)
DECLARE @NEW VARCHAR(50),@INDEX INT,@TEMP VARCHAR(50)
IF LEFT(@S,1)<>@SPLIT
SET @S = @SPLIT+@S
IF RIGHT(@S,1)<>@SPLIT
SET @S = @S+@SPLIT
WHILE CHARINDEX(@SPLIT,@S)>0 AND LEN(@S)<>1
BEGIN
SET @INDEX = CHARINDEX(@SPLIT,@S)
SET @TEMP = LEFT(@S,CHARINDEX(@SPLIT,@S,@INDEX+LEN(@SPLIT)))
IF @NEW IS NULL 
SET @NEW = ISNULL(@NEW,'')+@TEMP
ELSE
SET @NEW = ISNULL(@NEW,'')+REPLACE(@TEMP,@SPLIT,'')+@SPLIT
WHILE CHARINDEX(@TEMP,@S)>0
BEGIN
SET @S=STUFF(@S,CHARINDEX(@TEMP,@S)+LEN(@SPLIT),CHARINDEX(@SPLIT,@S,CHARINDEX(@TEMP,@S)+LEN(@SPLIT))-CHARINDEX(@TEMP,@S),'')
END
END
RETURN RIGHT(LEFT(@NEW,LEN(@NEW)-1),LEN(LEFT(@NEW,LEN(@NEW)-1))-1)
END

4、用于查找某一个子字符串在整个字符串的出现次数

ALTER FUNCTION [dbo].[fnGetRepeatCharNum]
(    
    @CodeString varchar(2000),
    @RepeatString varchar(200)
)
RETURNS int 
AS
BEGIN 
    DECLARE
        @RepeatStringLen int,    --检测重复字符的长串
        @TempString varchar(500)    --临时字符串(总比@RepeatString多一个即长度大1)
    
    SET @RepeatStringLen=LEN(@RepeatString)
    SET @TempString=Replicate(',',@RepeatStringLen)+','

    RETURN LEN(REPLACE(@CodeString,@RepeatString,@TempString))-LEN(@CodeString)
END

 

5、用于显示数据表某一列的所有内容相加

select top 1 userid, (select ','+ltrim(u_cname) from sys_User for xml path('')) as allu_cname from sys_User b  

 

6、查找相邻节点

 //select top 1 GroupID from sys_Group where G_ShowOrder < 3 and G_Level = 3 and G_ParentID = 221 order by G_ShowOrder asc 当前节点的上一个节点
 //select top 1 GroupID from sys_Group where G_ShowOrder > 3 and G_Level = 3 and G_ParentID = 221 order by G_ShowOrder asc 当前节点的下一个节点