一、問題描述
最近,一個(gè)統(tǒng)計(jì)程序從Oracle移植到PostgreSQL(版本9.4)時(shí),接連報(bào)告錯(cuò)誤:
錯(cuò)誤信息1: postgresql group by position 0 is not in select list.
錯(cuò)誤信息2: non-integer constant in GROUP BY.
產(chǎn)生錯(cuò)誤的sql類似于:
insert into sum_tab (IntField1, IntField2, StrField1, StrField2, cnt)
select IntField, 0, StrField, 'null', count(*) from detail_tab
where ...
group by IntField, 0, StrField, 'null';
其中,detail_tab表保存原始的詳細(xì)記錄,而sum_tab保存統(tǒng)計(jì)后的記錄信息。
二、原因分析
經(jīng)過測(cè)試,發(fā)現(xiàn)錯(cuò)誤是因?yàn)镻ostgreSQL對(duì)GROUP BY子句使對(duì)使用常量有著特殊限制。測(cè)試過程過于繁瑣,這里不再一一寫demo了,直接給出結(jié)論:
1 GROUP BY子句中不能使用字符串型、浮點(diǎn)數(shù)型常量, 否則會(huì)報(bào)告錯(cuò)誤信息2。如:
select IntField, 'aaa', count(*) from tab group by IntField, 'aaa';
select IntField, 0.5, count(*) from tab group by IntField, 0.5;
2 GROUP BY子句中也不能使用0和負(fù)整數(shù),否則會(huì)報(bào)錯(cuò)誤信息1。如:
select IntField, 0, count(*) from tab group by IntField, 0;
select IntField, -1, count(*) from tab group by IntField, -1;
那么,GROUP BY子句中可以使用什么類型的常量?經(jīng)測(cè)試,在常用的類型中,正整數(shù)、日期型常量均可以。
select IntField, 1, count(*) from tab group by IntField, 1;
select IntField, now(), count(*) from tab group by IntField, now();
對(duì)于第一節(jié)中的sql,因?yàn)?和‘null'有著特殊的含義,該如何處理?
實(shí)際上,在GROUP BY子句中可以不使用任何常量,只列出聚集字段即可,即將第一節(jié)中的sql改為:
insert into sum_tab (IntField1, IntField2, StrField1, StrField2, cnt)
select IntField, 0, StrField, 'null', count(*) from detail_tab
where ...
group by IntField, StrField;
三、MySQL的情況
考慮到將來統(tǒng)計(jì)程序也可能移植到MySQL(版本8.x),隨后進(jìn)行了類似測(cè)試,結(jié)論為:
1 支持不帶任何常量的GROUP BY子句;
2 支持帶非0整數(shù)、浮點(diǎn)數(shù)(包括0.0)、字符串、日期型常量的GROUP BY子句。
也就是說,在常見類型中,MySQL 8的GROUP BY子句支持除整數(shù)0(非浮點(diǎn)數(shù)0.0)以外的所有類型。否則,會(huì)報(bào)錯(cuò):
ERROR 1054 (42S22): Unknown column '0' in 'group statement'
順便說一句,Oracle對(duì)整數(shù)0也支持。
四、結(jié)論
1、PostgreSQL的GROUP BY子句只支持正整數(shù)、日期型的常量;
2、MySQL支持除非0整數(shù)以外的所有常規(guī)類型常量,而Oracle似乎全部支持;
3、如果有在各各數(shù)據(jù)庫平臺(tái)可移植的需求,盡量不要在GROUP BY子句中使用常量。
補(bǔ)充:PostgreSQL的GROUP BY問題
關(guān)于PostgreSQL數(shù)據(jù)庫分組查詢時(shí),跟mysql還是有區(qū)別的。糾結(jié)了半天
SELECT
prjnumber,
zjhm,
-- to_char ( to_timestamp ( kqsj / 1000 ), 'yyyy-MM-dd HH24:MI:SS' ) kqsj,
kqflag,
workername,
max(kqsj)
-- workertype,
-- tpcodename,
-- isactive
FROM
GB_CLOCKINGIN
WHERE
kqsj BETWEEN 1590940800000 AND 1593532799000
AND prjnumber = '3205842019121101A01000'
GROUP BY
zjhm,
kqflag,
prjnumber,
workername
以上為個(gè)人經(jīng)驗(yàn),希望能給大家一個(gè)參考,也希望大家多多支持腳本之家。如有錯(cuò)誤或未考慮完全的地方,望不吝賜教。
您可能感興趣的文章:- PostgreSQL中的VACUUM命令用法說明
- PostgreSQL 如何查找需要收集的vacuum 表信息
- PostgreSQL備份工具 pgBackRest使用詳解
- postgresql數(shù)據(jù)合并,多條數(shù)據(jù)合并成1條的操作
- postgresql數(shù)據(jù)添加兩個(gè)字段聯(lián)合唯一的操作
- postgreSQL使用pgAdmin備份服務(wù)器數(shù)據(jù)的方法
- Postgresql 查看SQL語句執(zhí)行效率的操作