我在Windows上运行Oracle 12.1。我需要帮助创建一个列表聚合的日期（全天是一天的条件，trunc（日期））和地点，并计算清单结果中的库存类型的金额。我计划将其作为car_equipment表的视图来显示列表聚合。
在显示max（date）的库存列表之前，我做了一个简单的视图。

CREATE OR REPLACE FORCE NONEDITIONABLE VIEW "DB1"."CAR_INVENTORY_VW" ("MAX_INVENTORY_DATE","LOCATION", "INVENTORY_LIST") AS 
 SELECT 
  inventory_date max_inventory_date,
  location,
  LISTAGG(num_cars || ' ' || equipment_type, ', ') 
    WITHIN GROUP (ORDER BY equipment_type) inventory_list
      FROM (
        SELECT c.*, RANK() OVER(PARTITION BY location ORDER BY inventory_date DESC) rn
            FROM car_equipment c
           ) x 
           WHERE rn = 1
            GROUP BY inventory_date, location;

但现在我想显示一个地点的所有日期，并按日期收集每个地点的库存类型计数的总和。下面是创建表和行的代码：首先创建表并插入数据条件示例行。

CREATE TABLE "CAR_EQUIPMENT" 
   ("NUM_CARS" NUMBER(10,0), 
"EQUIPMENT_TYPE" VARCHAR2(100 BYTE), 
"LOCATION" VARCHAR2(500 BYTE), 
"INVENTORY_DATE" DATE) 
SEGMENT CREATION IMMEDIATE 
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
NOCOMPRESS LOGGING
   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
   TABLESPACE "USERS" ;

INSERT INTO "CAR_EQUIPMENT" (NUM_CARS, EQUIPMENT_TYPE, LOCATION, INVENTORY_DATE) VALUES ('8', 'Rovers', 'coventry', TO_DATE('2019-09-07 09:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "CAR_EQUIPMENT" (NUM_CARS, EQUIPMENT_TYPE, LOCATION, INVENTORY_DATE) VALUES ('1', 'Rovers', 'coventry', TO_DATE('2019-09-07 18:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "CAR_EQUIPMENT" (NUM_CARS, EQUIPMENT_TYPE, LOCATION, INVENTORY_DATE) VALUES ('9', 'Jaguars', 'coventry', TO_DATE('2019-09-07 06:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "CAR_EQUIPMENT" (NUM_CARS, EQUIPMENT_TYPE, LOCATION, INVENTORY_DATE) VALUES ('7', 'Rovers', 'leamington', TO_DATE('2019-08-30 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "CAR_EQUIPMENT" (NUM_CARS, EQUIPMENT_TYPE, LOCATION, INVENTORY_DATE) VALUES ('10','Trans Am', 'leamington', TO_DATE('2019-08-30 09:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "CAR_EQUIPMENT" (NUM_CARS, EQUIPMENT_TYPE, LOCATION, INVENTORY_DATE) VALUES ('2','Trans Am', 'leamington', TO_DATE('2019-08-30 18:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "CAR_EQUIPMENT" (NUM_CARS, EQUIPMENT_TYPE, LOCATION, INVENTORY_DATE) VALUES ('8', 'Rovers', 'coventry', TO_DATE('2019-09-06 18:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "CAR_EQUIPMENT" (NUM_CARS, EQUIPMENT_TYPE, LOCATION, INVENTORY_DATE) VALUES ('4', 'Rovers', 'leamington', TO_DATE('2019-09-06 09:00:00', 'YYYY-MM-DD HH24:MI:SS'));

commit;

我找不到可以在listagg中获得sum（num_汽车）的示例。
另外，（我不想在这里问一个后续问题），但是如果列表聚合超过4000字节会发生什么？我读到可以使用xmlagg，但是如果结果超过4000字节，显示列表的最佳方式是什么呢？你以前遇到过这个问题吗？你是如何解决这个问题的？再次感谢您的任何建议。最亲切的问候，吉利
我试过：

create or replace view TEST_LAGG(
  WITH
  TEMP AS (SELECT LOCATION,
                  trunc(INVENTORY_DATE) inventory_date,
                  EQUIPMENT_TYPE,
                  sum(NUM_CARS) sum_num_cars
           from CAR_EQUIPMENT
           group by LOCATION,
                    trunc(INVENTORY_DATE),
                    EQUIPMENT_TYPE
         )
   select LOCATION,
          INVENTORY_DATE,
          listagg(sum_num_cars ||' '|| EQUIPMENT_TYPE, ', ') within group (order by inventory_date) lagg
   from TEMP
   group by LOCATION,
        inventory_date);

但我得到ORA-00903：无效表名00903。00000 -“无效的表名”
再次感谢Littlefoot，我尝试了：

create or replace view TEST_LAGG
 AS WITH
  TEMP (SELECT LOCATION,
                  trunc(INVENTORY_DATE) inventory_date,
                  EQUIPMENT_TYPE,
                  sum(NUM_CARS) sum_num_cars
           from CAR_EQUIPMENT
           group by LOCATION,
                   trunc(INVENTORY_DATE),
                    EQUIPMENT_TYPE
         )
    select LOCATION,
      INVENTORY_DATE,
      listagg(sum_num_cars ||' '|| EQUIPMENT_TYPE, ', ') within group   (order by inventory_date) lagg
 from TEMP
 group by LOCATION,
        inventory_date;

但我得到了错误：ORA-00903：无效的表名00903。00000 -“无效的表名”* 原因：

• 操作：行出错：135柱：3 -TEMP表参考线。