mysql - 在已知深度的层次结构中计算值

我必须查询2个表中的一些派生值。其简化结构如下：

用户数

用户具有一个ID和一个父级列，该列表示其父级的ID。每个用户还有一个佣金值，该佣金值表示他们从该行的员工那里获得的销售额百分比。

只有员工才能进行销售，并且该信息记录在下表中

+------------------------------------+
| ID Name          Parent Commission |
+------------------------------------+
| 1  SeniorManager NULL   5          |
| 2  Manager       1      10         |
| 3  Employee1     2      13         |
| 4  Employee2     2      12         |
+------------------------------------+

营业额

该表记录了通过其ID链接的员工的销售额。它记录销售金额以及销售时间。

+---------------------------+
| user_id amount created_at |
+---------------------------+
| 3       100    2014-01-16 |
| 3       120    2014-01-16 |
| 3       110    2014-01-16 |
+---------------------------+

从系统的其他部分，我知道给定用户ID的用户深度。在实际的系统中，有7个固定级别，但是出于问题的考虑，在此我将其简化。

我要写的查询是：给定SeniorManager的ID和日期范围，显示其下的经理列表，这些经理的汇总佣金以及该经理期望的佣金。因此，鉴于以上数据，我们期望：

+--------------------------------------------+
| Name    Sales ManagerCommission Commission |
+--------------------------------------------+
| Manager 330   33.30             16.65      |
+--------------------------------------------+

我到目前为止的查询是：

SELECT
    users.name AS Name,
    SUM(sales.amount) AS Sales,
    SUM(sales.amount) * (users.commission/100) AS ManagerCommission
FROM
    users
LEFT JOIN users AS employees
    ON employees.parent = users.id
LEFT JOIN sales
    ON sales.id = employees.id AND
    sales.created_at BETWEEN DATE(?) AND DATE(?)
WHERE
    users.parent = ?
GROUP BY
    users.name

我不确定如何获得按经理而不是按员工分组的佣金的最后一列值。另一个附带的问题是，有没有一种方法可以重用在select语句中两次使用的SUM(sales.amount)。我宁愿不两次计算完全相同的值。我计划针对每个已知深度编写7个查询。有更有效的方法吗？

最佳答案

为每个管理级别添加一个联接：-

SELECT
    senior_manager.name AS Name,
    SUM(sales.amount) AS Sales,
    SUM(sales.amount * manager.commission/100) AS ManagerCommission,
    SUM(sales.amount) * (senior_manager.commission/100) AS SeniorManagerCommission
FROM
    users AS senior_manager
LEFT JOIN users AS manager
    ON manager.parent = senior_manager.id
LEFT JOIN users AS employees
    ON employees.parent = manager.id
LEFT JOIN sales
    ON sales.id = employees.id AND
    sales.created_at BETWEEN DATE(?) AND DATE(?)
WHERE
    senior_manager.id = ?
GROUP BY
    senior_manager.name

SQL摆弄它：-

http://www.sqlfiddle.com/#!2/2cfffe/4

关于mysql - 在已知深度的层次结构中计算值，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/21156846/