聚合函数 listagg （超出长度限制时xmlagg）

 

表&数据

CREATE TABLE peoplebuy (people Varchar2(10),buy Varchar2(10),price NUMBER);
INSERT INTO peoplebuy VALUES ('我','小猫',10);
INSERT INTO peoplebuy VALUES ('我','小狗',200);
INSERT INTO peoplebuy VALUES ('你','树',20);

原来的结果

SELECT * FROM PEOPLEBUY ORDER BY PEOPLE;

想要的结果

SELECT PEOPLE,
       LISTAGG(BUY, ',') WITHIN GROUP(ORDER BY BUY DESC) AS BUY,
       SUM(PRICE) TOTAL
  FROM