首先创建group_concat
聚集函数:
CREATE AGGREGATE group_concat(anyelement)(sfunc = array_append, -- 每行的操作函数,将本行append到数组里stype = anyarray, -- 聚集后返回数组类型initcond = '{}' -- 初始化空数组);
接着上一个SQL
样例:
po
和season
分组,把ticket_code
和order_id
去除重复并且拼接起来 --wp_order_detailSELECT po, season, array_to_string( group_concat ( DISTINCT ticket_code ), ',' ) AS ticket_codes, array_to_string( group_concat ( DISTINCT order_id ), ',' ) AS order_idsFROM wp_order_detailWHERE exists (select 1 from wp_order orders where orders.id = order_id and type = 'Po')GROUP BY po, season;
最后查询结果截图: