LEFT JOIN 查询中的 SUM
问题描述：
我有两张桌子:
Table products
ID Name Base
1 Product_1 5
2 Product_2 4
Table orders
ID Product_ID
1 1
2 1
3 2
我正在使用这个查询:
SELECT products.ID, products.Base, COUNT(orders.ID) AS Counter
FROM products
LEFT JOIN orders ON products.ID = orders.Product_ID
GROUP BY products.ID
获得:
ID Base Counter
1 5 2
2 4 1
我现在想要做的是编写一个查询，该查询将返回一个类似于上面的表，但带有额外的列 SUM - Base 和 Counter 的总和:
What I want to do now is to write a query that's going to return a table like one above but with extra column SUM - sum of Base and Counter:
ID Base Counter SUM
1 5 2 7
2 4 1 5
是否可以使用单个查询来实现此结果?我还想按 SUM 列对我的结果进行排序.
Is it possible to achieve this result using single query? I'd also like to order my results by SUM column.
答
不要想太多.只需用加号将它们加在一起即可.
Don't overthink it. Just add them together with a plus sign.
SELECT products.ID, products.Base, COUNT(orders.ID) AS Counter,
products.Base + COUNT(orders.ID) as `SUM`
FROM products
LEFT JOIN orders ON products.ID = orders.Product_ID
GROUP BY products.ID
ORDER BY `SUM`
注意:SUM"是聚合 SQL 函数的名称，因此您需要用反引号将其括起来，以便将其用作列别名.考虑使用不同的名称.
Note: "SUM" is the name of an aggregate SQL function so you'll need to surround it in backticks in order to use it as a column alias. Consider using a different name.