MySQL 查询闭包表

Question

当前 USER 表：

Field       Type        
id          int(11)     
firstname   varchar(64)     
lastname    varchar(64)     
status      varchar(5)  
permission  smallint(5) 

当前 USER_RELATIONS 表：（它有 2 个外键 - 祖先 - > USER.id - 后代 - > USER.id）

Field       Type 
relationId  int(11)
ancestor    int(11)
descendant  int(11)
length      int(11)

当前 TRANSACTIONS 表：（它有 1 个外键 - chid - > USER.id）

Field   Type
id      int(11)
chid    int(11)
date    date
amt     varchar(16)

所有关系均已正确设置，并在用户加入并输入引用他的另一个用户的引用时创建，从而创建层次结构树。

之前，我试图正确地进行此设置，并得到了“Puggan Se”的大力帮助，他为我指明了 Closure Tables 的方向。

目前，我可以看到单个原始推荐人（祖先）及其所有被推荐用户（后代）的整个树。我们还设置了一个自动分级系统，可以对推荐了预定义数量后代的每个用户进行分级。

我们现在已经添加了一个商店，希望给每个用户一点东西，供他们参考并邀请其他用户加入和购物。 “小东西”基本上是销售额的百分比，基于其树结构。

解释：

1: A invited B, C & D -> B invited E,F -> C invited G -> D invited no-one
2: A moves to status=2(because he invited 3) -> B moves to status=1 (cause he invited 2)-> C & D remain on status=0 (because minimum required invites = 2)
3: Now when B, C & D purchases something from the shop, A should ge a little something back. Because A is status 2, he will get X % for all status=1 sales and Y % for all status=0 sales
4: In the event that B surpasses A in status, A will NOT get a "little something" back.
5: status=0 levels do not get something back.

问题： 我希望有人检查 MySQL 查询并告诉我是否正确执行。我想获取祖先树中所有后代的总交易和总支出，其中后代状态 <祖宗地位。有人可以帮忙吗？目前，我为每个关系运行 4 次。length/status=，因为有 4 个状态级别低于最高级别 4。

查询：

select COUNT(*) as total, SUM(amt) as amount from transactions
  left join card_holders_relations as t1 on transactions.chid = t1.descendant
  left join card_holders as t2 on t2.id = t1.descendant
  where t1.ancestor = '3'
  AND t2.status = 0
  AND t1.length = 4;

现在每次 t2.status 增加到不等于祖先状态并且 t1.length 减少时到 1，因为 length = 0 是祖先本身。

我的假设和方法正确还是有更简单的方法？