gpt4 book ai didi

SQL GROUP BY 和 HAVING

转载 作者:行者123 更新时间:2023-12-02 19:31:49 26 4
gpt4 key购买 nike

所以我不会再遇到这个错误。

 select distinct substr(CUSTZIP, 1,5), AVG(CUSTBAL), custcity, custstate
from customer
group by CUSTCITY, custstate
having CUSTSTATE = 'wa' AND avg(CUSTBAL) >100;

错误显示“不是 GROUP BY 表达式”,并建议将“substr(CUSTZIP, 1,5), AVG(CUSTBAL)”添加到 group by 子句中,但这也不起作用。我想要做的是仅列出西澳州各城市的邮政编码和余额平均值,并且余额超过 100 美元。有人可以帮我指出我的错误吗?我确信它很简单,但我似乎无法以我的初学者水平得到它。

最佳答案

问题不在于 having 子句。它是 substr(CUSTZIP, 1, 5)。这是解决该问题的一种方法:

select substr(CUSTZIP, 1, 5), AVG(CUSTBAL), custcity, custstate
from customer
group by CUSTCITY, custstate, substr(CUSTZIP, 1, 5)
having CUSTSTATE = 'wa' AND avg(CUSTBAL) > 100;

顺便说一下,group by 几乎不需要 select unique

或者,您可以使用聚合函数:

select max(substr(CUSTZIP, 1, 5)), AVG(CUSTBAL), custcity, custstate
from customer
group by CUSTCITY, custstate
having CUSTSTATE = 'wa' AND avg(CUSTBAL) > 100;

关于SQL GROUP BY 和 HAVING,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/26007959/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com