Oracle SQL LISTAGG函数如何为每个聚合值添加计数？

要实现将每个航班编号与对应的计数拼接后聚合，需要先统计每个分组内各航班的次数，再结合LISTAGG完成拼接。以下是具体实现方案：

方法一：子查询拼接后聚合 #

SELECT 
  CLIENT,
  AIRLINE,
  COUNT(DISTINCT t.CUSTOMER_ID) AS DISTINCT_CUSTOMER,
  COUNT(t.ORDER_ID) AS NO_ORDERS,
  LISTAGG(f.FLIGHT || ' (' || f.FLIGHT_COUNT || ')', ', ') 
    WITHIN GROUP (ORDER BY f.FLIGHT) AS FLIGHT
FROM your_table_name t
JOIN (
  -- 先统计每个客户、航司下各航班的订单次数
  SELECT 
    CLIENT,
    AIRLINE,
    FLIGHT,
    COUNT(*) AS FLIGHT_COUNT
  FROM your_table_name
  GROUP BY CLIENT, AIRLINE, FLIGHT
) f ON t.CLIENT = f.CLIENT AND t.AIRLINE = f.AIRLINE
GROUP BY CLIENT, AIRLINE

方法二：用CTE优化可读性 #

如果希望逻辑更清晰，可以使用公共表表达式（CTE）拆分步骤：

WITH flight_counts AS (
  SELECT 
    CLIENT,
    AIRLINE,
    FLIGHT,
    COUNT(*) AS FLIGHT_COUNT
  FROM your_table_name
  GROUP BY CLIENT, AIRLINE, FLIGHT
)
SELECT 
  t.CLIENT,
  t.AIRLINE,
  COUNT(DISTINCT t.CUSTOMER_ID) AS DISTINCT_CUSTOMER,
  COUNT(t.ORDER_ID) AS NO_ORDERS,
  LISTAGG(f.FLIGHT || ' (' || f.FLIGHT_COUNT || ')', ', ') 
    WITHIN GROUP (ORDER BY f.FLIGHT) AS FLIGHT
FROM your_table_name t
JOIN flight_counts f ON t.CLIENT = f.CLIENT AND t.AIRLINE = f.AIRLINE
GROUP BY t.CLIENT, t.AIRLINE

注意事项 #

• 替换your_table_name为实际的表名；
• 若需要统计航班对应的不同客户数而非订单数，将子查询/CTE中的COUNT(*)改为COUNT(DISTINCT CUSTOMER_ID)即可；
• 若FLIGHT字段可能为空，可通过NVL(FLIGHT, '未知航班')处理空值，避免拼接出无效内容。

内容的提问来源于stack exchange，提问作者user15676