1.使用自定义聚合函数 group_concat
SELECT * FROM
(
SELECT "FFID",CAST ("BRDNO" AS INTEGER),"count" (*) AS "NUM",
array_to_string(group_concat("PRSTA"), ',') AS "PRSTA"
FROM "metaHeader" A,"PNOPPNCK" b
WHERE
A .metaid = b.metaid
AND "FLIGHTDATE" = '20181028'
GROUP BY "FFID","BRDNO"
)
ORDER BY "FFID","BRDNO"
********************************************************************
2. 使用自带聚合函数 string_agg查询
说明:string_agg更加耗时。
--1.自定义聚合函数group_concat
CREATE AGGREGATE group_concat(anyelement)
(
sfunc = array_append, -- 每行的操作函数,将本行append到数组里
stype = anyarray, -- 聚集后返回数组类型
initcond = '{}' -- 初始化空数组
);