MySql:从另一个表中通过其父表的ID获取表的总和，并返回所有具有与parent_id相关的总和值的子级 [英] MySql: Getting sum of a table by the id of its parent from other table and return all children with sum values that are relevant to parent_id

问题描述

想象一下这些是我数据库的表:

Imagine these are my database's tables:

Table #1: child
╔══════════╦═════════════════╦═══════╗
║Child_id  ║Primary key - AI ║ int   ║
╠══════════╬═════════════════╬═══════╣
║parent_id ║Index-Forign_key ║ int   ║
║══════════╬═════════════════╬═══════╣
║title     ║        -        ║varchar║                   
╚══════════╩═════════════════╩═══════╝

---

Table #2: Paid
╔══════════╦═════════════════╦═══════╗
║Paid_id   ║Primary key - AI ║ int   ║
╠══════════╬═════════════════╬═══════╣
║child_id  ║Index-Forign_key ║ int   ║
║══════════╬═════════════════╬═══════╣
║paid_price║        -        ║int    ║                   
╚══════════╩═════════════════╩═══════╝

---

这是我的表值:

---

Here are my tables value:

Table: child (data)
╔══════════╦══════════╦═══════╗
║ Child_id ║parent_id ║ title ║
╠══════════╬══════════╬═══════╣
║ 1        ║25        ║bla-bla║
║══════════╬══════════╬═══════╣
║ 2        ║25        ║bla-bla║
║══════════╬══════════╬═══════╣
║ 3        ║5         ║bla-bla║
║══════════╬══════════╬═══════╣
║ 4        ║25        ║bla-bla║
╚══════════╩══════════╩═══════╝

 Table: paid (data)
╔══════════╦══════════╦════════════╗
║ Paid_id  ║Child_id  ║ paid_price ║
╠══════════╬══════════╬════════════╣
║ 1        ║1         ║100         ║
║══════════╬══════════╬════════════╣
║ 2        ║2         ║250         ║
║══════════╬══════════╬════════════╣
║ 3        ║1         ║35          ║
║══════════╬══════════╬════════════╣
║ 4        ║1         ║17          ║
╚══════════╩══════════╩════════════╝

我有一个查询，该查询从子表中返回(获取)所有child_id，然后通过该ID返回每个子表的pay_price总和.

I have a query that returns (get) all child_id from child table, then by that ids return sum paid_price of each of them.

这是我的查询:

SELECT child.Child_id, child.parent_id, sum(paid.paid_price)
FROM paid
JOIN child ON child.Child_id = paid.Child_id
WHERE child.parent_id =25
group by child.Child_id, child.parent_id

结果和演示

RESULT AND DEMO

我想要这个:现在我的查询运行良好，但是当 paid 表中没有 child_id 时，查询就不会退还它.我的意思是我希望所有 child_id 天气是否都在 paid 表中.如果它们不返回0 (现在我认为它为null且不返回)

I WANT THIS: Now my query is working well, but when there is no child_id in the paid table, the query don't return it. I mean I want all child_id weather they are in paid table or not. if they are not return 0 (now I think it is null and does not return)

请查看我的演示(上面的链接)，并看到如果我输入 parent_id = 25 ，它将返回2行(总和)，因为 child_id = 3 不会存在于 paid 中，不会在结果中返回

Please see my demo (link above) and see that if I enter parent_id=25 it will return 2 rows (sum), because child_id = 3 does is not exist in paid and does not return it in result

推荐答案

您需要的是左联接(并反转表的顺序)或不太常见的右联接:

What you need here is a LEFT JOIN (and reverse the order of the tables) or a RIGHT JOIN which is less common:

select
  parent.Child_id,
  parent.parent_id,
  coalesce(sum(paid.paid_price), 0)
from
  parent left join paid
  on parent.Child_id = paid.Child_id
where
  parent.parent_id =25
group by
  parent.Child_id,
  parent.parent_id
;

LEFT JOIN将返回左侧表中的所有行，以及右侧表中的所有匹配行.如果不匹配，则右侧表格中的列将为空.

a LEFT JOIN will return all rows from the table on the left, and all matching rows from the table on the right. If no match, the columns from the table on the right will be null.

Coalesce将返回第一个非null值，因此，如果sum()为null，则将返回0.

Coalesce will return the first non null value, so if sum() is null it will return 0 instead.

这篇关于MySql:从另一个表中通过其父表的ID获取表的总和，并返回所有具有与parent_id相关的总和值的子级的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！