如何在LISTAGG范围内仅分组指定列，排除其他列聚合并添加关联字段

你可以先通过子查询（或CTE）完成LISTAGG的聚合计算，再将结果与Table3关联，这样就无需把address和pin加入GROUP BY子句：

WITH PurchasedProducts AS (
    SELECT
      t1.ID,
      LISTAGG(t2.product, ', ') WITHIN GROUP (ORDER BY t2.product) AS Purchased_Products
    FROM
      Table1 t1
    JOIN
      Table2 t2 ON t1.ID = t2.ID
    GROUP BY
      t1.ID
)
SELECT
  pp.ID,
  pp.Purchased_Products,
  t3.address,
  t3.pin
FROM
  PurchasedProducts pp
JOIN
  Table3 t3 ON pp.ID = t3.ID;

逻辑说明 #

1. 先在CTE PurchasedProducts中完成按ID分组的LISTAGG聚合，得到每个ID对应的已购产品列表，这一步完全保留你原来的分组逻辑。
2. 再将这个聚合结果与Table3通过ID关联，由于Table3中每个ID对应唯一的address和pin值，直接查询这两个字段即可，不需要额外分组。

执行后得到的结果会是：

+----+----------------------+----------+-----+
| ID | Purchased_Products   | address  | pin  |
+----+----------------------+----------+------+
| 1  | laptop, phone        | bgr      | 432  |
| 2  | book, headset        | chennai  | 434  |
| 3  | iphone               | mumbai   | 445  |
+----+----------------------+----------+------+

如果不想用CTE，也可以用子查询实现：

SELECT
  sub.ID,
  sub.Purchased_Products,
  t3.address,
  t3.pin
FROM
  (
    SELECT
      t1.ID,
      LISTAGG(t2.product, ', ') WITHIN GROUP (ORDER BY t2.product) AS Purchased_Products
    FROM
      Table1 t1
    JOIN
      Table2 t2 ON t1.ID = t2.ID
    GROUP BY
      t1.ID
  ) sub
JOIN
  Table3 t3 ON sub.ID = t3.ID;

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