string_agg去重功能

Question

比如有以下采购记录表: pu_order

supplier | product | date | qty

---

supA | pc001 | 2025-01-01 | 3

supA | pc002 | 2025-01-02 | 1

supB | pc001 | 2025-01-03 | 6

supB | pc001 | 2025-01-05 | 8

supA | pc001 | 2025-01-06 | 6

我想统计1月份，每种产品都采购过多少，供应商都有哪几家，用以下代码后，得到的供应商会出现重复，请问如何去重呢？

select product, 总数量=sum(qty), 供应商=string_agg(supplier, ';')

from pu_order

where [date] between '2025-01-01' and '2025-01-31'

goup by product

Answer 1

你好，

请尝试如下代码：

;WITH CTE AS
(
select product, 总数量=sum(qty), supplier
from pu_order
where [date] between '2025-01-01' and '2025-01-31'
group by product,supplier
)
SELECT product, 总数量=sum(总数量), 供应商=string_agg(supplier, ';')
FROM CTE
GROUP BY product

---

如果答案是正确的解决方案，请点击“接受答案”并投赞成票。 如果您对此答案有其他疑问，请点击“评论”。