postgreSQL 错误 42803：我不应该收到此错误，这是怎么回事？

Question

这是我第一篇关于堆栈溢出的文章。每隔一次我需要这里的东西时，就会有人问我这个问题。我找不到任何类似这样的问题，所以我想我会发一个新帖子。

我的SQL查询有点问题，错误代码对我来说没有多大意义

SELECT customer.account as "Account", customer.name as "Customer Name", sum(slpayment.payment_amount) as "Payment Amount", slpayment.reference, max(date(slpayment.payment_date)) as "Payment Date",  
CASE WHEN customer.userchk1 = '1' then 'B' else ' Y' END as type  
FROM customer  
INNER JOIN slpayment ON customer.company = slpayment.company and  
customer.account = slpayment.account  
WHERE slpayment.sales_period='11'

此查询正在标记SQL错误42803："错误：列"customer.account"必须出现在GROUP BY子句中或用于聚合函数">

我熟悉这个错误，并已设法在其他查询中解决了它。然而，我不明白的是为什么我会犯这个错误。有问题的查询在函数中的任何位置都没有GROUP BY子句。我看了一下其他人，他们在这里提出了关于堆栈溢出的类似问题，但他们所有的查询都包括GROUP BY，因此他们得到错误的原因是有道理的。我不知道为什么我在这里遇到麻烦。

提前感谢您的光临！

Answer 1

您需要添加group by子句并应用除聚合列之外的所有列

SELECT customer.account as "Account", customer.name as "Customer Name", sum(slpayment.payment_amount) as "Payment Amount", slpayment.reference, max(date(slpayment.payment_date)) as "Payment Date", CASE WHEN customer.userchk1 = '1' then 'B' else ' Y' END as type 
FROM customer INNER JOIN slpayment 
ON customer.company = slpayment.company and customer.account = slpayment.account 
WHERE slpayment.sales_period='11'
group by customer.account , customer.name ,slpayment.reference,customer.userchk1

Answer 2

如果您使用像sum,avg,count,min,max这样的聚合函数，则必须使用group bycluase，因此，尽管您在查询中没有使用group by，但如果您在查询中将使用聚合函数，如果在选择中有任何非聚合列，则postgray引擎将建议您在查询

读取聚合功能文档

所以你的查询会像下面的一样

SELECT customer.account as "Account", customer.name as "Customer Name", sum(slpayment.payment_amount) as "Payment Amount", slpayment.reference, max(date(slpayment.payment_date)) as "Payment Date",  
CASE WHEN customer.userchk1 = '1' then 'B' else ' Y' END as type  
FROM customer  
INNER JOIN slpayment ON customer.company = slpayment.company and  
customer.account = slpayment.account  
WHERE slpayment.sales_period='11'
group by customer.account,customer.name,slpayment.reference

Answer 3

我建议横向连接：

SELECT c.account, c.name as "Customer Name", 
p."Payment Amount", p.reference, 
max(date(p.payment_date)) as "Payment Date",
(case when c.userchk1 = '1' then 'B' else ' Y' end) as type 
FROM customer c CROSS JOIN LATERAL
(SELECT p.reference,
sum(p.payment_amount) as "Payment Amount",
max(date(p.payment_date)) as "Payment Date"
FROM slpayment p
WHERE c.company = p.company AND 
c.account = p.account AND
p.sales_period = '11'
) p;

据推测，您实际上并没有聚合customers中的行，只是聚合支付表中的数据。如果是这样的话，它应该有更好的性能。