我想要一个SQL查询,它同时执行INNER JOIN
和GROUP BY
.我try 了以下不起作用的方法:
SELECT customer.first_name, SUM(payment.amount)
FROM customer
GROUP BY customer.customer_id
INNER JOIN payment
ON payment.customer_id = customer.customer_id;
提前谢谢!
我想要一个SQL查询,它同时执行INNER JOIN
和GROUP BY
.我try 了以下不起作用的方法:
SELECT customer.first_name, SUM(payment.amount)
FROM customer
GROUP BY customer.customer_id
INNER JOIN payment
ON payment.customer_id = customer.customer_id;
提前谢谢!
首先,GROUP BY
出现在查询的末尾(如果有的话,就在order by
或having
子句之前).
然后,select中不在聚合函数中的所有字段都必须在group by子句中.
所以
SELECT customer.first_name, SUM(payment.amount)
FROM customer
INNER JOIN payment
ON payment.customer_id = customer.customer_id
GROUP BY customer.first_name;
但是名字相同的客户会被分组,这可能不是你真正想要的.
所以 rather
SELECT customer.first_name, SUM(payment.amount)
FROM customer
INNER JOIN payment
ON payment.customer_id = customer.customer_id
GROUP BY customer.first_name, customer.customer_id;