postgresql group by 报错
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了postgresql group by 报错,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含3184字,纯文字阅读大概需要5分钟。
内容图文
举个例子: ????table?name:makerar cname??|?wmname?|??????????avg??????????? --------+-------------+------------------------ ? canada?|?zoro???|?????2.0000000000000000 spain??|?luffy??|?1.00000000000000000000 ? spain??|?usopp??|?????5.0000000000000000 ? 执行语句: ?1 SELECT cname, wmname, MAX(avg) FROM makerar GROUP BY cname;? ? 错误: ???? ERROR:??column?"makerar.wmname"?must?appear?in?the?GROUP?BY?clause?or?be?used?in?an? aggregate?function? LINE?1:?SELECT?cname,?wmname,?MAX(avg)??FROM?makerar?GROUP?BY?cname; ???? ???? ???? ????首先这个错误的原因是因为:?
Before SQL3 (1999), the selected fields must appear in the GROUP BY clause[*].Interestingly enough,
even though the spec sort of allows to select non-grouped fields, major engines seem to not really like it.
Oracle and SQLServer just don't allow this at all. Mysql used to allow it by default, but now since 5.7 the
administrator needs to enable this option (ONLY_FULL_GROUP_BY) manually in the server configuration
for this feature to be supported.
???? ?? 翻译过来就主要是:在SQL3(1999)标准之前,select 的字段必须也放在group by 的语句里(因为当如未 在group的相同字段出现不同值时,数据库引擎便不知道刚显示什么了,如上例)。主要的数据库引擎都不允 许这样的操作(有selected?field?不放在group?by中),即使mysql在5.7版本后也需要打开一个选项才能使用。 ? ? ? 这种操作在mysql上运行的情况:it?doesn't?work?"well"?in?mysql?--?in?fact,?they?actually?warn?you?in?the?docs that?if?you?do?it,?and?all?the?"hidden"?columns?(those?not?in?the?GROUP?BY)?aren't?1-to-1?with?the?GROUP?BY? columns,?then?the?results?are?unpredictable?in?every?other?database?you?just?plain?can't?do?it,?so?i?wouldn't?call? what?mysql?does?"doing?it?well"。 ???? 解决办法: ??????? 1.先在子查询里进行聚合运算(sum,max等),在通过join连接 ???????? ??1 SELECT m.cname, m.wmname, t.mx FROM ( SELECT cname, MAX(avg) AS mx 2 FROM makerar GROUP BY cname ) t 3 JOIN makerar m ON m.cname = t.cname AND t.mx = m.avg ;
? cname??|?wmname?|??????????mx??????????? --------+--------+------------------------ ?canada?|?zoro???|?????2.0000000000000000 ? ?spain??|?usopp??|?????5.0000000000000000 ???????? ???????? ?????? 2.使用window functions ? ? ? ?? ?1 SELECT cname, wmname, MAX(avg) OVER (PARTITION BY cname) AS mx FROM makerar;? ???????? ?cname??|?wmname?|??????????mx??????????? --------+--------+------------------------ ?canada?|?zoro???|?????2.0000000000000000 ? ?spain??|?luffy??|?????5.0000000000000000 ? ?spain??|?usopp??|?????5.0000000000000000 ? ? ????????要去掉mx重复的话: ? ? ? ?
SELECT DISTINCT /* distinct here matters, because maybe there are various tuples for the same max value */ m.cname, m.wmname, t.avg AS mx FROM ( SELECT cname, wmname, avg, ROW_NUMBER() OVER (PARTITION BY avg DESC) AS rn FROM makerar ) t JOIN makerar m ON m.cname = t.cname AND m.wmname = t.wmname AND t.rn = 1 ;
? cname??|?wmname?|??????????mx??????????? --------+--------+------------------------ ? canada?|?zoro???|?????2.0000000000000000 ? spain??|?usopp??|?????5.0000000000000000 ???????? ??? 3.使用 DISTINCT ON ????
1 SELECT DISTINCT ON (cname) cname, wmname, avg FROM makerar ORDER BY cname, avg DESC ;
内容总结
以上是互联网集市为您收集整理的postgresql group by 报错全部内容,希望文章能够帮你解决postgresql group by 报错所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。