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 当前节点的下一个节点

 

 

 

转载于:https://www.cnblogs.com/zgqys1980/archive/2012/12/05/2803047.html

总结

以上是生活随笔为你收集整理的sql语句总结一的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。