带LEFT JOIN的SQL查询问题 [英] SQL Query with LEFT JOIN Issue

问题描述

我在使用左联接SQL查询时遇到问题，但是看不到为什么它不起作用.我有3个表格:客户，购买和付款，并且我试图选择总购买成本低于其总付款(即余额大于0)的客户.

I am having problems with a left join SQL query but can't see why it isn't working. I have 3 tables: customers, purchases and payments, and i'm trying to select customers who's total purchases cost is less than their total payments (i.e. they have a balance greater than 0).

到目前为止，我有以下内容:

So far, I have the following:

表格:

Customers

id | Name


Purchases

id | customerid | cost


Payments

id | customerid | paymentamount 

SQL查询:

SELECT  a.*, 
COALESCE(b.totalCost , 0) as totalCost, 
COALESCE(c.totalPayments , 0) as totalPayments, 
COALESCE(b.totalCost , 0) - COALESCE(c.totalPayments , 0) AS Balance 
FROM customers a 
LEFT JOIN (SELECT  customerid, SUM(cost) AS totalCost FROM purchases GROUP BY customer) b ON a.id = b.customerid 
LEFT JOIN (SELECT customerid, SUM(paymentamount) AS totalPayments FROM payments GROUP BY customerid) c ON a.id = c.customerid 
WHERE Balance > 0"

运行查询时，即使定义了Balance，我也会在"where子句"中得到错误消息未知列" Balance".

When I run the query, I get the error 'Unknown column 'Balance' in 'where clause'' even though I have defined Balance.

我们非常感谢您的帮助.谢谢！

Any help is much appreciated. Thanks!

推荐答案

，因为BALANCE是表达式中给出的ALIAS.使用WHERE子句而不是ALIAS

because BALANCE is an ALIAS given on the expression. Use the expression on the WHERE clause instead of the ALIAS

WHERE   COALESCE(b.totalCost , 0) - COALESCE(c.totalPayments , 0) > 0

另一种方法是用子查询包装整个语句，以便您可以在外部查询的WHERE子句上使用别名.

the other way is to wrap the whole statement with a subquery so you can use the alias on the WHERE clause of the outer query.

之所以不能使用在WHERE子句的同一级别上创建的ALIAS的原因是，因为WHERE子句比创建ALIAS的SELECT子句首先执行.

The reason why you cannot use ALIAS that is created on the same level of the WHERE clause is because WHERE clause executes first than the SELECT clause where the ALIAS is created.

这是SQL的操作顺序:

Here's the SQL Order of Operation:

• FROM子句
• WHERE子句
• GROUP BY子句
• HAVING子句
• SELECT子句
• ORDER BY子句

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