MySQL - 父+子类别总数

Question

我有两个表:

父子“类别”:

id    name         parent_id
1     Food         NULL
2     Pizza        1
3     Pasta        2

'交易':

id     amount      category_id
1      100         1
2      50          2
3      25          2

我想返回所有类别以及总计两列:

total = 具有此 category_id 的所有交易的金额总和

parentTotal = total + 所有子类的总和

示例(使用上面的表格):

id    name         parent_id    total    parentTotal
1     Food         NULL         100      175
2     Pizza        1            0        0
3     Pasta        2            75       0

编辑:

代码已更新(基于下面 Nedret Recep 的代码)并且工作正常...

SELECT 
    tmp1.id, tmp1.name, tmp1.parent_id, tmp1.total, IFNULL(tmp1.total, 0) + IFNULL(tmp2.s, 0)  AS parenttotal
   FROM
        (SELECT
                ca.id, ca.name, ca.parent_id, SUM(tr.amount) as total
        FROM
            categories ca

        LEFT JOIN
                transactions tr
        ON
                tr.category_id = ca.id 
        GROUP BY
                ca.id)
    AS tmp1     

LEFT JOIN 
        (SELECT
            c.id, c.parent_id as categoryid, SUM(t.amount) AS s  
        FROM
            transactions t
        RIGHT JOIN
            categories c
        ON
            t.category_id = c.id
        GROUP BY 
            c.parent_id)
    AS tmp2

ON tmp2.categoryid = tmp1.id 

order by coalesce(tmp1.parent_id, tmp1.id), tmp1.parent_id

非常感谢您的帮助 - 谢谢!

Answer 1

通过一个内部联接，我们计算标准类别中的总数。然后使用另一个内部联接，我们计算总和，但这次按 parent_id 分组。然后我们将两个结果表连接起来，使两个总和都在一行中。对于大型表，此查询会很慢，因此应用程序级别的替代方法会做得更好。

  SELECT 
        tmp1.id, tmp1.name, tmp1.parent_id, tmp1.total, tmp1.total + tmp2.s AS parenttotal
   FROM
   (SELECT
        ca.id, ca.name, ca.parent_id, SUM(tr.amount) as total
   FROM
        transactions tr
   INNER JOIN
        categories ca
   ON
        tr.categoru_id = ca.id 
   GROUP BY
        ca.id)AS tmp1     
   LEFT OUTER JOIN 
   (
   SELECT
     c.parent_id as categoryid, SUM(t.amount) AS s  
   FROM
      transactions t
   INNER JOIN
      categories c
   ON
      t.category_id = c.i
   GROUP 
       BY c.id ) AS tmp2
   ON
       tmp2.categoryid = tmp.id