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 當前節點的下一個節點

?

?

?

轉載于:https://www.cnblogs.com/zgqys1980/archive/2012/12/05/2803047.html

總結

以上是生活随笔為你收集整理的sql语句总结一的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。