如何从相关表求和

aydmsdu9 于 2021-06-15 发布在 Mysql

关注(0)|答案(3)|浏览(346)

我有一个主表和一个子表。主表是部件的主列表，子表是构建它所需的集合部件。每个子零件都通过parentid链接到其父零件。
零件或“child”有一个childid，它指向主零件列表中自己的条目。我们使用这个链接来获取它的价格，因为在子表中，只存储id。
如果我想把构成每个父部分的所有子部分的总成本加起来。我将如何构造该查询？
total cost应该是一个计算字段，它选择子表中parentid==到其id的所有部分，并将它们的价格*数量相加。
以下是我目前掌握的情况：

//This Query Doesnt work. Just returns 0.
SELECT SUM(PricePer * Qty)
    FROM sub inner join main on sub.ChildPID = main.ID where sub.ParentPID = main.ID

//This query will work, where the main.ID is entered manually. but I cant do this
SELECT SUM(PricePer * Qty)
    FROM sub inner join main on sub.ChildPID = main.ID where sub.ParentPID = 1

例子：
主表

ID | Name | TotalCost | PricePer 

1    Part1  10.00       14.00** SELECTED

2    Part2  0.00        6.00

3    Part3  0.00        2.00

子表

ParentID | ChildID | Name | Qty

1          2         Part2  1

1          3         Part3  2

sql mysql mysql-workbench

来源：https://stackoverflow.com/questions/54112619/how-to-sum-from-a-related-table

3条答案

按热度按时间

roejwanj1#

不应该在左连接中使用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

赞(0）回复(0）举报 2021-06-16

wsewodh22#

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

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

赞(0）回复(0）举报 2021-06-16

jgwigjjp3#

下面是一个级别的选择：

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;

赞(0）回复(0）举报 2021-06-15

我来回答

如何从相关表求和

3条答案

相关问题

热门标签

最新问答