新手问题 同样的查询,在 PostgreSQL 中查询快让我抓狂了!

kai209209 · 2014年08月26日 · 最后由 iBachue 回复于 2014年08月27日 · 12791 次阅读

现做一项目,之前是使用 mysql,现在改为使用 pg。

在没改数据库之前,一切查询都是很正常的,改了之后就发现很多问题,然后一直纠结这些问题。 比如现有一条简化版的查询

ProOutStock.select("fitting_barcode").group("fitting_item_id")

在 mysql 数据库是可以执行的(我直接在数据库下执行命令,因为是描述问题,所以没有带数据)

mysql> select fitting_barcode from pro_out_stocks group by fitting_item_id;Empty set (0.00 sec)

mysql> 

在 pg 中是这样的

repair_facility=# select fitting_barcode from pro_out_stocks group by fitting_item_id;
ERROR:  column "pro_out_stocks.fitting_barcode" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select fitting_barcode from pro_out_stocks group by fitting_...
               ^
repair_facility=# 

就直接报错了,然后我就上 stackoverflow 查相关问题。 然后查到说是 mysql 并不是完全严格的执行 sql 的标准。 这个 fitting_barcode 可能在 fitting_item_id 一样的的情况下出现不同的内容,所以 pg 才会出现报错,然后他们就提议使用 DISTINCT ON 来解决该问题,所以我执行

repair_facility=# select DISTINCT ON (fitting_barcode) fitting_barcode from pro_out_stocks group by fitting_item_id;
ERROR:  column "pro_out_stocks.fitting_barcode" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select DISTINCT ON (fitting_barcode) fitting_barcode from pr...
                                             ^
repair_facility=# 

问题依然存在,这个时候或许有人说既然 fitting_barcode 是都是一样的,就提议在 groub by 中加入 fitting_barcode 输出如下

ProOutStock.select("fitting_barcode").group("fitting_item_id, fitting_barcode")

repair_facility=# select DISTINCT ON (fitting_barcode) fitting_barcode from pro_out_stocks group by fitting_item_id, fitting_barcode;
 fitting_barcode 
-----------------
(0 rows)

repair_facility=# 

也许上面这个是可以查询出来的,但是问题是我实际应用可能不止那么简单的查询,比如 我的查询可能是下面这样的或者更复杂包括 sum, where,having 什么的 比如下面这个

ProOutStock.select("fitting_barcode,fitting_name, SUM(price_int) AS  prices, .......").group("fitting_item_id, company_id")

我这里将("fitting_item_id,company_id")一样的数据的 price_int SUM 出来。 因为这事我已经弄了 2 天了,还没解决,所以现在有点迷茫。

所以不知道有没有对 postgresql 比较熟悉的来解答一下我该如何解决这个问题呢?

错误都已经提示得很清楚了呀

ERROR:  column "pro_out_stocks.fitting_barcode" must appear in the GROUP BY clause or be used in an aggregate function

select 的字段必须 group,sum 的字段会把一致的集合相加,肯定不用加在 group by 里的 这个没有问题

楼主快要认识到同样是 SQL 数据库但都有各自的规范的了。

这个 case 中的 sql 在 mysql 中能执行,是 mysql 配置不严格而给出的执行结果,这个 sql 本身就是不对的。 请自行搜索一下 aggregate function 和 window function

怪到 mysql 上了。。。

PostgreSQL 里规律比较严格 我在用的时候也明显感觉到了,但是规律严格也确实有它的道理,服从就是了。 但是只是这么一点点限制楼主竟然能弄两天 我就没啥想法了。

MySQL: 怪我咯?

#4 楼 @chaosong 同意。 sql 有问题。 group by 的含义是把一些特征相同的行,集合起来处理,比如针对某个列 sum。

LZ 的 sql 究竟是想做什么,这是个根本问题。 如果只是要得到表里的 distinct fitting_barcode,直接 distinct 就行了。

#1 楼 @hz_qiuyuanxin 知道他的提示了,主要之前一直在纠结为什么 mysql 可以的,pg 不可以而已

#2 楼 @azhao 从严格的 sql 语句来讲,是必须要加入的,因为我对数据库不了解导致出现该问题

#3 楼 @Rei Thank!

#4 楼 @chaosong Thank!

#6 楼 @iBachue 我转行做开发不到 10 个月呢,新手一枚,仍处于学习状态中,对于这种偶尔钻牛角尖也是可以理解的吧,比较对于 rails 跟 ruby 来说,数据库是自己的一个弱点啊,悲剧😭

#8 楼 @zjlovezj 其实就是要获得某个值的 SUM,之前一直在纠结为什么 mysql,而 pg 里不可以了,现在明白为什么了!

#9 楼 @kai209209 看上去楼主要先从数据库原理学起 虽然很学术 但是掌握之后 楼主使用任何 SQL 数据库都不会有什么困难了

需要 登录 后方可回复, 如果你还没有账号请 注册新账号