存储过程 给表排序

前家公司用到过没想到很快又遇到这问题了 那就照搬吧 不一样的地方也就表名与字段名

现在数据表需要用到排序功能，正好跟之前排序功能一样 干脆照搬一下前人写的代码觉得以后还会继续用到 就留着吧

期间也是用到一级 二级排序功能

USE [IstudyMMMMMMM]
GO
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE  [dbo].[HobbyTypeOrder]
AS
BEGIN
 --处理一级类型内部排序
 UPDATE dbo.S_HobbyType SET innerOrderID = X.innerOrderID
	FROM S_HobbyType 
	JOIN (SELECT T.TypeID,T.TypeName,T.level,T.ParentID, ROW_NUMBER() OVER (ORDER BY T.innerOrderID) innerOrderID 
		FROM dbo.S_HobbyType T 
		WHERE  T.level = 1)
	 X 
	 ON X.TypeID = S_HobbyType.TypeID;
	  --处理二级类型内部排序
	 UPDATE dbo.S_HobbyType SET innerOrderID = X.innerOrderID
	FROM S_HobbyType 
	JOIN (SELECT T.TypeID,T.TypeName,T.level,T.ParentID, ROW_NUMBER() OVER (PARTITION BY T.ParentID ORDER BY T.innerOrderID) innerOrderID 
		FROM dbo.S_HobbyType T 
		WHERE    T.level = 2)
	 X 
	 ON  X.TypeID = S_HobbyType.TypeID;
 
   --处理全局排序号
 WITH CET AS
	(
		SELECT  L.TypeID,l.ParentID,L.level, dbo.CalcTreeNodeOrder(9,1024,level,InnerOrderID, NULL) GlobalOrder FROM dbo.S_HobbyType L WHERE   level = 1
		UNION ALL
		SELECT L.TypeID,L.ParentID,L.level, dbo.CalcTreeNodeOrder(9,1024,L.level,L.InnerOrderID,CET.GlobalOrder) GlobalOrder FROM dbo.S_HobbyType L JOIN CET ON L.ParentID = CET.TypeID
	)

	UPDATE dbo.S_HobbyType SET GlobalOrderID = X.GlobalOrderID
	FROM S_HobbyType JOIN (SELECT TypeID,ROW_NUMBER() OVER (ORDER BY CET.GlobalOrder) GlobalOrderID FROM CET) X ON X.TypeID = dbo.S_HobbyType.TypeID;
  
END

 CalcTreeNodeOrder：

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO

ALTER FUNCTION [dbo].[CalcTreeNodeOrder]
(
	@MaxLevel SMALLINT,
	@MaxCount BIGINT,
	@CurLeval SMALLINT,
	@CurOrder BIGINT,
	@PrtOrder VARBINARY(8000)
)
RETURNS VARBINARY(8000)
AS
BEGIN
	 DECLARE @LevelByteLength INT =  LOG(@MaxCount) / LOG(256) + 1
	 DECLARE @RESULT VARBINARY(8000) = NULL
 
	 --设置父级节点信息
	 IF @CurLeval > 1
		BEGIN
			 SET @RESULT = SUBSTRING(@PrtOrder,1,(@CurLeval - 1) * @LevelByteLength) + CASE @LevelByteLength 
			 WHEN 1 THEN CONVERT(VARBINARY(1),@CurOrder)
			 WHEN 2 THEN CONVERT(VARBINARY(2),@CurOrder)
			 WHEN 3 THEN CONVERT(VARBINARY(3),@CurOrder)
			 WHEN 4 THEN CONVERT(VARBINARY(4),@CurOrder)
			 WHEN 5 THEN CONVERT(VARBINARY(5),@CurOrder)
			 WHEN 6 THEN CONVERT(VARBINARY(6),@CurOrder)
			 WHEN 7 THEN CONVERT(VARBINARY(7),@CurOrder)
			 WHEN 8 THEN CONVERT(VARBINARY(8),@CurOrder)
		 END
	 END
	 ELSE
		 SET @RESULT = CASE @LevelByteLength 
		 WHEN 1 THEN CONVERT(VARBINARY(1),@CurOrder)
		 WHEN 2 THEN CONVERT(VARBINARY(2),@CurOrder)
		 WHEN 3 THEN CONVERT(VARBINARY(3),@CurOrder)
		 WHEN 4 THEN CONVERT(VARBINARY(4),@CurOrder)
		 WHEN 5 THEN CONVERT(VARBINARY(5),@CurOrder)
		 WHEN 6 THEN CONVERT(VARBINARY(6),@CurOrder)
		 WHEN 7 THEN CONVERT(VARBINARY(7),@CurOrder)
		 WHEN 8 THEN CONVERT(VARBINARY(8),@CurOrder)
	 END
       
	 WHILE @CurLeval < @MaxLevel
	 BEGIN
		 SELECT @RESULT = @RESULT + CASE @LevelByteLength 
		 WHEN 1 THEN 0x00
		 WHEN 2 THEN 0x0000
		 WHEN 3 THEN 0x000000
		 WHEN 4 THEN 0x00000000
		 WHEN 5 THEN 0x0000000000
		 WHEN 6 THEN 0x000000000000
		 WHEN 7 THEN 0x00000000000000
		 WHEN 8 THEN 0x0000000000000000
		 END,@CurLeval = @CurLeval + 1
	 END  
	 RETURN @RESULT
END
GO

 

转载于:https://www.cnblogs.com/xuanlanbinfen/p/5776609.html