按查询展开分组中的分组行

以下面的数据为例来说明我在寻找什么。以下示例数据中只有两个表。 pymt\u事务和事务\u项 .

WITH pymt_transactions AS
(
  SELECT 1 AS tran_id FROM dual UNION
  SELECT 2 AS tran_id FROM dual UNION
  SELECT 3 AS tran_id FROM dual UNION
  SELECT 4 AS tran_id FROM dual UNION
  SELECT 5 AS tran_id FROM dual
) /* END pymt_transactions CTE */
--SELECT * FROM pymt_transactions;
, transaction_items AS
(
  SELECT 1 AS tran_id, 'T-Shirt' AS retail_item, 15 AS amt FROM dual UNION
  SELECT 1 AS tran_id, 'Shoes'   AS retail_item, 50 AS amt FROM dual UNION
  SELECT 1 AS tran_id, 'Pants'   AS retail_item, 40 AS amt FROM dual UNION
  SELECT 1 AS tran_id, 'Comb'    AS retail_item, 3  AS amt FROM dual UNION
  SELECT 2 AS tran_id, 'Sweater' AS retail_item, 15 AS amt FROM dual UNION
  SELECT 2 AS tran_id, 'Belt'    AS retail_item, 12 AS amt FROM dual UNION
  SELECT 2 AS tran_id, 'Pants'   AS retail_item, 40 AS amt FROM dual UNION
  SELECT 2 AS tran_id, 'Watch'   AS retail_item, 23 AS amt FROM dual
) /* END transaction_items CTE */

SELECT pt.tran_id, ti.retail_item, ti.amt
FROM pymt_transactions pt
  LEFT JOIN transaction_items ti ON ti.tran_id = pt.tran_id
ORDER BY pt.tran_id
;

上面的结果生成下面的结果集。

SELECT pt.tran_id, COUNT(ti.retail_item) AS num_retail_items, 
NVL(SUM(ti.amt),0) AS payment_amount
FROM pymt_transactions pt
  LEFT JOIN transaction_items ti ON ti.tran_id = pt.tran_id
GROUP BY pt.tran_id
ORDER BY pt.tran_id
;

上面的按查询分组生成以下结果和按事务id分组: