mysql - 从mysql中的二叉树获取记录

Question

我需要知道如何从由某些记录组成的表中获取记录，该记录随名为[sponser_id]的不同id而变化

从上面的图片中我给你一个简单的场景..比如说:圆形的人据说是人A。

• 当 A 登录他/她的帐户时。它应该显示有多少成员(计数)在他/她的控制之下。
• Mysql 表具有诸如
  之类的列
• sponser_id 指的是父 user_id。
• sponser_id 因推荐他们的parent_user而异。
• 我的问题是，如何检索该特定人员下的成员数量..
• 我的表格中只有 user_id 和 sponser_id 列。

Answer 1

这是我曾经在这里实现的传销树，我想我删除了答案，因为它没有得到赞赏:)

我总是使用存储过程来执行这些操作。成员可以有一个父级，就像您的设置一样。

输出显示下线销售额和 5% 的佣金

架构

-- drop table member;
create table member
(   memberId int not null auto_increment primary key,
    handle varchar(255) not null,
    parentId int null,
    key (parentId)
);


-- drop table sales
create table sales
(   -- sales of Products abbreviated
    id int auto_increment primary key,
    memberId int not null,
    amount decimal(10,2) not null,
    saleDate datetime not null,
    CONSTRAINT `fk_member`
        FOREIGN KEY (memberId) 
        REFERENCES member(memberId)
);

insert member(handle,parentId) values ('Johnny Two-Thumbs',null); -- 1
insert member(handle,parentId) values ('Jake the Enforcer',null); -- 2
insert member(handle,parentId) values ('Innocent Kim',2); -- 3
insert member(handle,parentId) values ('Karen',2); -- 4
insert member(handle,parentId) values ('Henry',2); -- 5
insert member(handle,parentId) values ('Shy Sales-less Sam',5); -- 6
insert member(handle,parentId) values ('Pete',5); -- 7
insert member(handle,parentId) values ('Iowa Mom',7); -- 8
insert member(handle,parentId) values ('Albuquerque Agoraphobiac',7); -- 9

insert sales (memberId,amount,saleDate) values (2,1,'2015-01-01');
insert sales (memberId,amount,saleDate) values (5,10,'2015-01-20');
insert sales (memberId,amount,saleDate) values (5,15.50,'2015-01-22');
insert sales (memberId,amount,saleDate) values (7,101.12,'2015-02-01');
insert sales (memberId,amount,saleDate) values (7,201.12,'2015-03-01');
insert sales (memberId,amount,saleDate) values (7,109,'2015-04-01');
insert sales (memberId,amount,saleDate) values (7,45,'2015-05-01');
insert sales (memberId,amount,saleDate) values (8,111,'2015-04-20');
insert sales (memberId,amount,saleDate) values (8,99.99,'2015-05-22');
insert sales (memberId,amount,saleDate) values (9,0.04,'2015-06-20');
insert sales (memberId,amount,saleDate) values (9,1.23,'2015-06-24');

存储过程

drop procedure if exists showAllDownlineSales;
DELIMITER $$
create procedure showAllDownlineSales 
(
theId int
)
BEGIN
    -- theId parameter means i am anywhere in hierarchy of membership
    -- and i want all downline sales
    -- return 1 row: sales amt for downlines, and that amt * 5%, and total children (including children-of-children)
    declare bDoneYet boolean default false;
    declare working_on int;
    declare theCount int;
    declare downlineSales decimal(10,2);
    declare commish decimal(10,2);

    CREATE temporary TABLE xxFindSalesxx
    (
        memberId int not null,
        processed int not null, -- 0 for not processed, 1 for processed
        salesTotal decimal(10,2) not null
    );

    set bDoneYet=false;
    insert into xxFindSalesxx (memberId,processed,salesTotal) select theId,0,0;
    while (!bDoneYet) do
        select count(*) into theCount from xxFindSalesxx where processed=0;

        if (theCount=0) then 
            -- found em all
            set bDoneYet=true;
        else
            -- one not processed yet, insert its children for processing
            SELECT memberId INTO working_on FROM xxFindSalesxx where processed=0 limit 1;

            insert into xxFindSalesxx (memberId,processed,salesTotal)
            select memberId,0,0 from member
            where parentId=working_on;

            -- update xxFindSalesxx
            -- join sales
            -- on sales.memberId=xxFindSalesxx.memberId
            -- set salesTotal=sum(sales.amount)
            -- where xxFindSalesxx.memberId=working_on;

            update xxFindSalesxx
            set salesTotal=(select ifnull(sum(sales.amount),0) from sales where memberId=working_on)
            where xxFindSalesxx.memberId=working_on;

            -- mark the one we "processed for children" as processed
            update xxFindSalesxx set processed=1 where memberId=working_on;
        end if;
    end while;

    delete from xxFindSalesxx where memberId=theId;
    select sum(salesTotal),count(*) into downlineSales,theCount from xxFindSalesxx;
    drop table xxFindSalesxx;
    select downlineSales,round(downlineSales*0.05,2) as commission,theCount;    -- there is your answer, 1 row
END
$$
DELIMITER ;

测试一下

call showAllDownlineSales(2); -- 693.00     34.69    7
call showAllDownlineSales(1); -- null       null     0
call showAllDownlineSales(5); -- 668.50     33.43    4