mysql - 一种更清洁的方法来执行此SQL查询

因此，有一个客户列表，即一个客户列表。

我有2个表，customers和invoices。

Customer_id对于我的客户可以相同，例如

客户A

Customer #1

客户B

Customer #1

因此，发票表的不同客户的customer_id可以为1。

以下查询有效，但看起来有些混乱。有什么想法可以清理吗？

  SELECT   name,
           sum(sub_total) total
    FROM   customers, invoices
   WHERE   customer_id = 1438 and
           invoices.customer_id = 1438 and
           customers.customer_id = invoices.customer_id
GROUP BY   name
Order By   total DESC;

最佳答案

如果A = B和A = 1438然后B = 1438，则无需检查...
 （请相信我，高中时我的数学达到60+）

SELECT   name, sum(sub_total) total
FROM     customers, invoices
WHERE    invoices.customer_id  = 1438
AND      customers.customer_id = invoices.customer_id
GROUP BY name
ORDER BY total DESC;

或明确地说出您想要的JOIN类型：

SELECT   name, sum(sub_total) total
FROM     customers  INNER JOIN invoices
ON       customers.customer_id = invoices.customer_id
WHERE    invoices.customer_id  = 1438
GROUP BY name
ORDER BY total DESC;