下面是一个级别的选择：

SELECT m.priceper + SUM(s1.qty * m2.priceper) AS priceper
FROM main m
LEFT JOIN sub s1 ON (m.id = s1.parentid)
LEFT JOIN main m2 ON (m2.id = s1.childid)
-- WHERE m.id = 1
GROUP BY m.id;

对于2个级别，它将是这样的（我无法尝试，可能需要一些修复）：

SELECT m1.id, m1.priceper + IFNULL(SUM(s1.qty * g2.priceper),0) AS priceper
FROM main m1
LEFT JOIN sub s1 ON (m1.id = s1.parentid)
LEFT JOIN (
    SELECT m2.id, m2.priceper + IFNULL(SUM(s2.qty * g2.priceper),0) AS priceper
    FROM main m2
    LEFT JOIN sub s2 ON (m2.id = s2.parentid)
    LEFT JOIN main g2 ON (g2.id = s2.childid)
    GROUP BY m2.id)
g2 ON (g2.id = s1.childid)
-- WHERE m1.id = 1
GROUP BY m1.id;

要深入研究，您需要用g3的子查询替换主表，等等。。。

SELECT m1.id, m1.priceper + IFNULL(SUM(s1.qty * g2.priceper),0) AS priceper
FROM main m1
LEFT JOIN sub s1 ON (m1.id = s1.parentid)
LEFT JOIN (
    SELECT m2.id, m2.priceper + IFNULL(SUM(s2.qty * g2.priceper),0) AS priceper
    FROM main m2
    LEFT JOIN sub s2 ON (m2.id = s2.parentid)
    LEFT JOIN (
        SELECT m3.id, m3.priceper + IFNULL(SUM(s3.qty * g3.priceper),0) AS priceper
        FROM main m3
        LEFT JOIN sub s3 ON (m3.id = s3.parentid)
        LEFT JOIN main g3 ON (g3.id = s3.childid)
        GROUP BY m3.id) g2 
        ON (g2.id = s2.childid)
    GROUP BY m2.id) g2 
    ON (g2.id = s1.childid)
-- WHERE m1.id = 1
GROUP BY m1.id;

对于4个级别，如下所示：

SELECT m1.id, m1.priceper + IFNULL(SUM(s1.qty * g2.priceper),0) AS priceper
FROM main m1
LEFT JOIN sub s1 ON (m1.id = s1.parentid)
LEFT JOIN (
    SELECT m2.id, m2.priceper + IFNULL(SUM(s2.qty * g2.priceper),0) AS priceper
    FROM main m2
    LEFT JOIN sub s2 ON (m2.id = s2.parentid)
    LEFT JOIN (
        SELECT m3.id, m3.priceper + IFNULL(SUM(s3.qty * g3.priceper),0) AS priceper
        FROM main m3
        LEFT JOIN sub s3 ON (m3.id = s3.parentid)
        LEFT JOIN (
            SELECT m4.id, m4.priceper + IFNULL(SUM(s4.qty * g4.priceper),0) AS priceper
            FROM main m4
            LEFT JOIN sub s4 ON (m4.id = s4.parentid)
            LEFT JOIN main g4 ON (g4.id = s4.childid)
            GROUP BY m4.id) g3
        ON (g3.id = s3.childid)
        GROUP BY m3.id) g2 
        ON (g2.id = s2.childid)
    GROUP BY m2.id) g2 
    ON (g2.id = s1.childid)
-- WHERE m1.id = 1
GROUP BY m1.id;

这变得比我想象的要复杂得多。也许这是我没说清楚的错。这是我发现有效的解决方案。

Select *, ifnull((SELECT SUM(main.PricePer * Qty)
    FROM sub inner join main on sub.ChildID = main.ID where sub.ParentID = main1.PID), 0) from main as main1

不应该在左连接中使用where子句，否则这将用作内部连接
如果你想把结果和上面的查询连接起来。。你应该按照你想要的与上面的查询相关的键来求和和和分组
你可以直接。。根据连接的结果执行更新，例如：

update main  
inner join  (
  SELECT  main.ID, SUM(main.PricePer) total_x_id
  FROM sub 
  INNER  join main on sub.ChildID = main.ID
  group by main.ID 
) t on t.id = main.id 
set total = t.total_x_id

如果你只需要选择

SELECT  main.ID, SUM(main.PricePer) total_x_id
 FROM sub 
 INNER  join main on sub.ChildID = main.ID
 group by main.ID

您可以在上面的查询中使用连接作为

select  maint.id, ifnull(t.total_x_id ,0)
from main 
left  join (
  SELECT  ChildID.ID as ID, SUM(main.PricePer) total_x_id
  FROM sub 
  group by ChildID.ID 
) t  on t.ID = main.id