LEFT JOIN 查询中的 SUM [英] SUM in LEFT JOIN query

问题描述

我有两张桌子:

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.

这篇关于LEFT JOIN 查询中的 SUM的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！