sqlserver 树形查找

WITH crm AS 
( 
	SELECT *,orderIndex=cast(MID  as varchar(max)) FROM IM_ChatRoomMessage 
	WHERE MID in (select MID from IM_ChatRoomMessage where ParentID is null and UserID = @userID) 
	UNION ALL 
	SELECT T.*,orderIndex=crm.orderIndex+cast(T.MID AS VARCHAR(MAX)) 
	FROM IM_ChatRoomMessage T,crm WHERE crm.MID=T.ParentID
) 
	SELECT MID,ParentID,UserID,Type,Content,Depth,CreateTime FROM crm  ORDER BY orderIndex 

根据最新回复排序

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
CREATE TABLE tmp_table(
	MID bigint  NOT NULL,ParentID bigint NULL,UserID int NULL,Type int NULL,Content nvarchar(max) NULL,Depth int NULL,CreateTime datetime NULL,orderIndex nvarchar (max) NULL,groupIndex bigint  NOT NULL,) ON [PRIMARY]

declare @mmid int
declare iterator cursor for
select MID from IM_ChatRoomMessage where ParentID is null and UserID = 166001
open iterator
fetch next from iterator into @mmid
while @@FETCH_STATUS = 0
begin
	WITH crm AS 
	( 
		SELECT *,orderIndex=cast(MID  as varchar(max))
		FROM IM_ChatRoomMessage 
		WHERE MID = @mmid 
		UNION ALL 
		SELECT T.*,orderIndex=crm.orderIndex+cast(T.MID AS VARCHAR(MAX))
		FROM IM_ChatRoomMessage T,crm WHERE crm.MID=T.ParentID
	) Insert into tmp_table SELECT *,groupIndex = (select MAX(MID) from crm) FROM crm ORDER BY orderIndex 
	fetch next from iterator into @mmid
end
close iterator
deallocate iterator
select * from tmp_table order by groupIndex desc,orderIndex
drop table tmp_table
COMMIT TRANSACTION;

查找top2

declare @mmid bigint
declare @tmp_table table(  
    MID bigint  not null,orderIndex nvarchar (max) NULL
 )
declare it cursor for
	select MID from @tmp_table
--查找根节点
insert into @tmp_table SELECT a.*,orderIndex=cast(MID  as varchar(max))
FROM IM_ChatRoomMessage a
WHERE (a.ParentID is null and 
	exists(select MID from IM_ChatRoomMessage e where e.ParentID = a.MID))
	
--查找单个子节点
open it
fetch next from it into @mmid
while @@FETCH_STATUS = 0
begin
	insert into @tmp_table
		select top 1 b.*,orderIndex=cast(@mmid AS VARCHAR(MAX))+cast(b.MID AS VARCHAR(MAX)) 
		from IM_ChatRoomMessage b
		where b.ParentID = @mmid
		fetch next from it into @mmid
end
close it
deallocate it
 
select * from @tmp_table order by MID,orderIndex

按回复排序

	declare @mmid bigint
	declare @innerIndex bigint
	declare @tmp_table table(  
		MID bigint  not null,Status int NULL,orderIndex int
	 )
	declare it cursor for
		select MID from @tmp_table where ParentID is null
	--查找根节点
	insert into @tmp_table SELECT a.*,orderIndex=MID
	FROM IM_ChatRoomMessage a
	WHERE (a.ParentID is null and 
		exists(select MID from IM_ChatRoomMessage e where e.ParentID = a.MID))
	--查找单个子节点
	open it
	fetch next from it into @mmid
	while @@FETCH_STATUS = 0
		begin
			set @innerIndex = (select top 1 MID
				from IM_ChatRoomMessage
				where ParentID = @mmid);
			update @tmp_table set orderIndex = @innerIndex where MID = @mmid;
			insert into @tmp_table
				select top 1 *,@innerIndex
				from IM_ChatRoomMessage
				where ParentID = @mmid;
			fetch next from it into @mmid;
		end
	close it
	deallocate it
	select MID,CreateTime,Status from @tmp_table order by orderIndex desc,ParentID

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!