按推荐人排序(相当复杂)

Question

在这个困境中，我将尽力做到简洁和充分的解释。

在我管理的一个网站上，我们允许经理查看他们的“招聘下线”，其中包括他们亲自招募的所有代理，以及特定代理(等等)给团队带来的招聘人员。

例如：

• 我招募两名特工，A和B。
• A招募两名特工，C和D。
• B招募两名特工，E和F。
• D招募两名探员G和H。
• C，E，F什么都不做。

​

​

在数据库中，每个单独的代理记录都有一个“referring agent”字段，其中列出了它们所招募的代理。

• 作为最高层，当我点击“我的新兵”时，我会看到一份所有下属的名单(因为他们都在我的保护伞下)。
• A可以看到C，D，G和H。
• B只能看到E和F，因为他们是他唯一的下线新兵，他们没有让任何人上船。

虽然这个功能很好用，但它存在缺陷有两个原因：

由于PHP脚本的构建方式，我们无法将佣金级别的数据作为一个整体进行排序。例子:即使我和高层都能看到每个人，按照‘佣金级别’对我的直属代理人进行分类，然后把他们的下线作为一个项目，然后根据我的标准继续进行排序。这一点很难理解，因此，假设下表显示了所有代理的“佣金水平”：

• A，7
• B，6
• C，5
• D，6
• E，5
• F，2
• G，5
• H，1

注:一名代理人绝不能以高于他们所坐的级别征聘另一名代理人，但他们可以在他们以下的任何级别征聘(例如，7名可以征聘1,2,3,4,5,6名，而3名只能征聘1，2名)。

从我(高层)的角度来看，

尽管按佣金级别对数据进行“排序”是有意义的，如: A、D、B、G、C、E、F、H--但情况并非如此。

相反(从顶级代理商的角度来看，你的看法)是: A，D，G，H，C，B，E，F

基本上，每隔一时间循环取决于直接上行代理号码，以确定谁落在下一行。

我知道这是很难理解的，但如果我能对我们目前的“类别”问题提供更多的理解，请告诉我。

Answer 1

我想我理解你了。您希望在给定的代理层次结构中按commission_level进行排序。以下内容可能会有所帮助(http://pastie.org/1111097)

drop table if exists agent;

create table agent
(
agent_id int unsigned not null auto_increment primary key,
name varchar(32) not null,
commission_level tinyint unsigned default 0,
parent_agent_id int unsigned default null
)
engine = innodb;

insert into agent (name, commission_level, parent_agent_id) values

('I', 99, null),
  ('A', 7, 1),
  ('B', 6, 1),
    ('C', 5, 2),
    ('D', 6, 2),
    ('E', 5, 3),
    ('F', 2, 3),
      ('G', 5, 5),
      ('H', 1, 5);


delimiter ;

drop procedure if exists agent_hier;

delimiter #

create procedure agent_hier
(
in p_agent_id int unsigned
)
proc_main:begin

declare done tinyint unsigned default 0;
declare dpth smallint unsigned default 0;


create temporary table hier(
 parent_agent_id int unsigned, 
 agent_id int unsigned, 
 depth smallint unsigned default 0
)engine = memory;

insert into hier values (p_agent_id, p_agent_id, dpth);

/* http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html */

create temporary table tmp engine=memory select * from hier;

while done <> 1 do

    if exists( select 1 from agent a inner join hier on a.parent_agent_id = hier.agent_id and hier.depth = dpth) then

        insert into hier 
            select a.parent_agent_id, a.agent_id, dpth + 1 from agent a
            inner join tmp on a.parent_agent_id = tmp.agent_id and tmp.depth = dpth;

        set dpth = dpth + 1;            

        truncate table tmp;
        insert into tmp select * from hier where depth = dpth;

    else
        set done = 1;
    end if;

end while;


select 
 a.agent_id,
 a.name as agent_name,
 if(a.agent_id = b.agent_id, null, b.agent_id) as parent_agent_id,
 if(a.agent_id = b.agent_id, null, b.name) as parent_agent_name,
 hier.depth,
 a.commission_level
from 
 hier
inner join agent a on hier.agent_id = a.agent_id
inner join agent b on hier.parent_agent_id = b.agent_id
order by
 -- dont want to sort by depth but by commision instead - i think ??
 -- hier.depth, hier.agent_id; 
 a.commission_level desc;

drop temporary table if exists hier;
drop temporary table if exists tmp;

end proc_main #


delimiter ;


/*

select * from agent;

call agent_hier(1);
call agent_hier(2);
call agent_hier(3);
call agent_hier(5);
*/

Answer 2

听起来你想在DB中实现类似树的结构。您是否考虑过使用Celko树：

http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

就我个人而言，我希望实现大多数排序，在DB中选择这类项目的各个方面。请注意，Celko树并不适合非常大的数据集。

Answer 3

所以问题是，您没有在数据库中存储“佣金级别”(我认为它是距离<∞的节点数)？

你有两个选择：

• 更改架构，使其易于检索。见这篇文章。
• 如果它不是一个选项，那么只使用MySQL中的SQL计算它可能是不可能的，因为您没有迭代查询(WITH RECURSIVE子句)。您必须在PHP中进行多个查询。