对于基于MLM的项目，以递归方式从表中获取值 [英] GET Values from table in a recursive manner for MLM based project

问题描述

我正在尝试使用C＃，我被困在这里。我的问题是我想要检索特定方面的所有用户的计数。让我解释。这是包含样本数据的表快照。



[URL = http://imageshack.us/photo/my-images/809/zdta.png/] [ IMG] http://imageshack.us/scaled/thumb/809/zdta.png [/ IMG] [/ URL]



现在我想要的是那个我得到了特定方面的用户数，因为我将用户ID作为参数。数据存储在MS Sql数据库中，我必须在网站上实现这一点，因此代码效率也是一个问题。



假设我输入1001作为输入

输出应为：左：1，右：8


如果输入1002则


输出应为：左：5，右：0



如果输入1003
输出应为：左：3，右：0



如果输入1004

输出应该是：左：0，右：0



如果输入1005

输出应为：左：1，右：1



等等。所以请给我一些关于如何实现这个东西的逻辑。

我还想得到一个代码，它将以分层方式返回特定ID的所有子用户的数组，以便我可以处理使用RadOrgChart控件的数据。

请帮助我。非常关键

解决方案

我认为这可以通过使用公用表表达式来解决。



检查此提示/技巧 - 公用表表达式查找所有相关节点 [ ^ ]



我修改了附有TIP的脚本添加了'Side'列并修改了CTE查询，我认为它有效..但我不确定，因为您的示例输入在问题中显示为笑脸（！）。以下是我写的CTE ..



让我知道这是否有效..

<预> 
   -   获取层次结构的CTE查询 
  DECLARE   @ EmpId   INT ; 
  SELECT   @ EmpId  =  5 ; 
 
  WITH 父 AS  
（
  SELECT  HE.EmpId，HE.EmpName，HE.EmpAge，HE.ManagerID，HE.Side 
  FROM  EmpMLM HE 
  WHERE  HE.EmpId =  @ EmpId  
  UNION   ALL  
  SELECT  HE.EmpId，HE .EmpName，HE.EmpAge，HE.ManagerID，HE.Side 
  FROM  EmpMLM HE  INNER   JOIN 父
 开 HE.EmpId = Parent.ManagerID 
  WHERE  
 HE.EmpId！= Parent.EmpId 
），
儿童
  AS  
（
  SELECT  *  FROM 父
  UNION   ALL  
  SELECT  HE.EmpId，HE.EmpName，HE.EmpAge，HE.ManagerID，HE.Side 
  FROM  EmpMLM HE 
  INNER   JOIN 子 ON  HE.ManagerID = Children。 EmpId 
  WHERE  
 HE.EmpId！= Children.EmpId 
）
  SELECT  SIDE，COUNT（MLM.Side） FROM  
（
  SELECT  *  FROM 父  -    GROUP BY Side  
  UNION  
  SELECT  *  FROM 儿童  -    G ROUP BY Side  
）MLM 
  GROUP   BY  Side 

I am trying to work with C# and i am stuck here. My problem is i want to retrieve the count of all the users in a specific side. Let me Explain. Here is my table snapshot with sample data.

[URL=http://imageshack.us/photo/my-images/809/zdta.png/][IMG]http://imageshack.us/scaled/thumb/809/zdta.png[/IMG][/URL]

Now what i want exactly is that i get the count of users in a specific side as i give a user id as a parameter.The data is stored in MS Sql Database and i have to implement this on a website so efficiency of code is also a issue.

Suppose i give 1001 as input
The output should be : Left:1 , Right: 8

if 1002 is input
The output should be : Left:5, Right:0

if 1003 is input
The output should be : Left:3, Right:0

if 1004 is input
The output should be : Left:0, Right:0

if 1005 is input
The output should be : Left:1, Right:1

and so on. So please provide me some logic on how to implement this thing.
I also want to get a code which would return an array of all child users of a particular ID in a hierarchical manner so that i could process that data with RadOrgChart Control.
Please help me.its very critical

解决方案

This I think can be solved by using a Common Table Expression.

Check this TIP / TRICK - Common Table Expression to find all related nodes[^]

I modified the script attached with the TIP and added the 'Side' column and modified the CTE query and I think it works.. But am not sure since your example inputs are shown as a smiley(!) in the question.. Given below is the CTE I wrote..

Let me know if this works..

<pre>
--CTE Query to get the hierarchy
DECLARE @EmpId INT;
SELECT @EmpId = 5;

WITH Parent AS 
(
	SELECT HE.EmpId, HE.EmpName, HE.EmpAge, HE.ManagerID, HE.Side 
	FROM EmpMLM HE
	WHERE HE.EmpId = @EmpId
	UNION ALL
	SELECT HE.EmpId, HE.EmpName, HE.EmpAge, HE.ManagerID , HE.Side
	FROM EmpMLM HE INNER JOIN Parent 
	On HE.EmpId = Parent.ManagerID
	WHERE 
	HE.EmpId != Parent.EmpId
), 
Children 
AS
(
	SELECT * FROM Parent 
	UNION ALL
	SELECT HE.EmpId, HE.EmpName, HE.EmpAge, HE.ManagerID , HE.Side  
	FROM EmpMLM HE
	INNER JOIN Children ON HE.ManagerID = Children.EmpId
	WHERE 
	HE.EmpId != Children.EmpId
)
SELECT SIDE , COUNT(MLM.Side) FROM 
(
SELECT * FROM Parent --GROUP BY Side
UNION 
SELECT * FROM Children --GROUP BY Side
) MLM
GROUP BY Side

这篇关于对于基于MLM的项目，以递归方式从表中获取值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！