求SQL查询计算多层次营销（MLM）团队营业额的解决方案

嘿，我来帮你搞定这个MLM团队订单金额统计的需求！先理清楚咱们手里的三张表结构和期望结果，然后直接上可行的SQL方案。

现有数据表 #

1. users表 #

ID  NAME
1   Robin
2   Edward
3   Donald
4   Julie

2. user_tree表（TREE字段是存储下属用户ID的JSON数组） #

ID  USER_ID  TREE
1   1        ["2","3","4"]
2   2        ["3","4"]
3   3        ["4"]
4   4        []

3. orders表 #

ID  AMOUNT  USER_ID
1   150     2
2   300     3
3   200     4
4   500     3

期望生成的mlm_team表 #

ID  USER_ID  TEAM_SUM
1   1        1150
2   2        1000
3   3        200

SQL实现方案 #

咱们的核心思路是：先把每个用户的下属ID数组拆成单独的行，再关联订单表求和，最后过滤掉无有效团队数据的用户。下面分两种主流数据库给出实现：

针对MySQL 8.0+的代码 #

MySQL 8.0及以上支持JSON_TABLE来解析JSON数组，直接用这个函数拆分就行：

CREATE TABLE mlm_team AS
SELECT
    ut.ID,
    ut.USER_ID,
    COALESCE(SUM(o.AMOUNT), 0) AS TEAM_SUM
FROM user_tree ut
LEFT JOIN JSON_TABLE(
    ut.TREE,
    '$[*]' COLUMNS(team_member_id INT PATH '$')
) AS tm ON TRUE
LEFT JOIN orders o ON tm.team_member_id = o.USER_ID
GROUP BY ut.ID, ut.USER_ID
HAVING TEAM_SUM > 0
ORDER BY ut.ID;

针对PostgreSQL的代码 #

PostgreSQL用unnest配合JSON解析函数来展开数组：

CREATE TABLE mlm_team AS
SELECT
    ut.ID,
    ut.USER_ID,
    COALESCE(SUM(o.AMOUNT), 0) AS TEAM_SUM
FROM user_tree ut
LEFT JOIN unnest(
    ARRAY(SELECT json_array_elements_text(ut.TREE))
) AS tm(team_member_id) ON TRUE
LEFT JOIN orders o ON tm.team_member_id::INT = o.USER_ID
GROUP BY ut.ID, ut.USER_ID
HAVING TEAM_SUM > 0
ORDER BY ut.ID;

代码简单解释 #

1. 拆分数组：用JSON_TABLE（MySQL）或unnest+json_array_elements_text（PostgreSQL）把每个用户的下属ID数组拆成一行一个ID的格式，方便后续关联。
2. 关联订单表：通过拆分出的下属ID关联订单表，拿到每个下属的订单金额。
3. 求和处理：用SUM计算下属的总金额，COALESCE确保没有订单的时候返回0而不是NULL。
4. 过滤无效数据：HAVING TEAM_SUM > 0把没有下属或者下属没订单的用户（比如USER_ID=4）排除掉。
5. 生成新表：CREATE TABLE ... AS直接把查询结果生成你需要的mlm_team表。

执行完上面的SQL，就能得到你想要的结果啦！

内容的提问来源于stack exchange，提问作者user2776338