通过sqlquery计算树的总配对成员数 [英] count total paired members of the tree by sqlquery

问题描述

我有一个代表树结构的表 tbltestingtree ， Member_Id 是该成员的id， Parent_Id 代表该成员的Parentid ，左撇子和权利告诉我们该成员附在父母的左侧或右侧。

我的问题是我必须创建一个sqlquery来计算任何特定父级的削减成员总数

I am having a table tbltestingtree representing the tree structure, Member_Id is the id of the member, Parent_Id represent the Parentid of that member , Lefts and Rights tells us that the member is attached to the left or right of the Parent.
My problem is that i have to make a sqlquery that counts the total number of pared members for any particular parent

Example -  currently member with id 1 has 3 pairs with id 2,3 and 4  
           currently member with id 2 has 1 pair with id  4

Member_Id     Parent_Id      Lefts         Rights
   1              Null       Null          Null
   2                1          1            Null
   3                1         Null           1
   4                2          1            Null
   5                2         Null           1
   6                3          1            Null
   7                3         Null           1
   8                4          1            Null
   9                4         Null           1

Image树在这里

http：// imageshack。 us / content_round.php？page = done& l = img4 / 4605 / 65ai.jpg [ ^ ]

推荐答案

嘿那里，



我知道这是一个迟到的回复，它已经解决了，但这是我试过的。



我创建了一个函数（递归）并称之为存储过程。



存储过程：

Hey there,

I know its a late reply and it has alreadt been solved, but here is what I tried.

I created a function (sort of recursive) and called it a stored procedure.

Stored Procedure:

CREATE PROCEDURE GetPairsCount
	@member int,
	@Count int output
AS
BEGIN

	Set @Count = dbo.GetTreePairsMethod(@member);
	
END

功能：

Function:

create FUNCTION GetTreePairsMethod 
(
	@member int
)
RETURNS int
AS
BEGIN
	Declare @result int = 0;
	if(Select count(*) from tree where parent = @member and ((lefts is not null and rights is null) or (lefts is null and rights is not null) ) ) = 2
	begin
		set @result = @result + 1;
		set @result += (Select  SUM(dbo.GetTreePairsMethod(memeber)) from tree where parent = @member and ((lefts is not null and rights is null) or (lefts is null and rights is not null) ) )
	end
	return @result
END

表字段可能稍微不同，例如memeber_id的memeber，但如果你将一个memberID传递给存储过程，它会获得该父母的削减成员数量。



Azee。 ..

Table fields may a little different, e.g, memeber for memeber_id, but if you pass a memberID to the stored procedure it gets you the number of pared members of that parent.

Azee...

最后我得到了答案：

Finally I Got My Answer :

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblTestingTree](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[ParentId] [int] NULL,
	[IsLeft] [bit] NULL,
	[IsRight] [bit] NULL,
 CONSTRAINT [PK_tblTestingTree] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[tblTestingTree] ON
INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (1, NULL, NULL, NULL)
INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (2, 1, 1, NULL)
INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (3, 1, NULL, 1)
INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (4, 2, 1, NULL)
INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (5, 2, NULL, 1)
INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (6, 3, 1, NULL)
INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (7, 3, NULL, 1)
INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (8, 4, 1, NULL)
INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (9, 4, NULL, 1)
INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (10, 5, 1, NULL)
SET IDENTITY_INSERT [dbo].[tblTestingTree] OFF



declare @ParentId as int
set @ParentId=2

create table #temp_table_name
(
 ParentId varchar(30) null,
 )

  
;with Child as
(
	select id,ParentId from tblTestingTree where id=@ParentId
	union all
	Select tblTestingTree.Id,tblTestingTree.parentId from tblTestingTree 
	inner join Child 
	on tblTestingTree.ParentId=Child.Id
)

insert into #temp_table_name
select c.ParentId  from tblTestingTree T join Child c
on c.Id=t.Id
WHERE ISNULL(T.ParentId, 0) <> 0 and c.ParentId!=@ParentId
group by c.ParentId
having COUNT(c.ParentId)>1 

select COUNT(*) from #temp_table_name

drop table #temp_table_name

这篇关于通过sqlquery计算树的总配对成员数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！