mysql - 参考其他两个表的总价格 - Mysql

Question

我有这三个表，我想获得链接到表 1 的表 3 的价格字段的总和，表 2 链接到表 1 和表 3：

表 1 - 技术

+--------------+------------+
|   id_tecnic  |   name     |
+--------------+------------+
|       1      |  Michael   |
|       2      |  Billy     |
|       3      |  Joe       |
+--------------+------------+

表 2 - 链接

+--------------+------------+------------+
|   id_linked  | id_tecnic  |    id_mer  |
+--------------+------------+------------+
|       1      |     1      |     4      |
|       2      |     3      |     1      |
|       3      |     3      |     2      |
+--------------+------------+------------+

表 3 - Mer

+--------------+------------+
|    id_mer    |    price   |
+--------------+------------+
|       1      |     30     |
|       2      |     70     |
|       3      |     50     |
|       4      |     10     |
+--------------+------------+

结果

+--------------+------------+-------------+
|   id_tecnic  |   name     | total_price |
+--------------+------------+-------------+
|       1      |  Michael   |      10     |
|       2      |  Billy     |       0     |
|       3      |  Joe       |      90     |
+--------------+------------+-------------+

我试过这个：

SELECT te.id_tecnic, 
SUM(m.price)
FROM tecnic te
LEFT JOIN linked l ON te.id_tecnic = l.id_tecnic
LEFT JOIN mer m ON l.id_mer = m.id_mer
GROUP BY te.id_tecnic

标签： mysqlsum

Answer 1

比利没有记录，Table 2 - Linked所以为了让比利在结果集中，你必须从 table 开始Table 3 - Mer，比如：

SELECT te.id_tecnic, te.name, SUM(m.price)
FROM mer m
LEFT jOIN linked l ON l.id_mer = m.id_mer
LEFT JOIN te ON te.id_tecnic = l.id_tecnic
GROUP BY te.id_tecnic;