mssql中将查询结果用逗号拼接
业务部门有个需求,付款申请中,采购订单与采购申请是1:N关系,且是多个采购订单一起付款,业务部门要求采购订单付款的时候,需要关联到对应的采购申请流程。
- 此处不讨论需求的合理性
问题不大,sql查询的时候将采购申请流程结果用逗号拼接即可,且字段赋予多流程即可。
但麻烦点就是这个sql写的麻烦,此文仅用于记录这个麻烦sql语句聊以备用。
CREATE view vm_mrg_purchase_order_pool_05 as
with a as
(select a.id,a.cgddh,b.dycgsqlc from uf_mrg_purchase_order_pool as a left join uf_mrg_purchase_order_pool_dt1 as b on a.id=b.mainid)
SELECT id,cgddh, STUFF((
SELECT DISTINCT ',' + CAST(dycgsqlc AS VARCHAR)
FROM a t2
WHERE t2.id = t1.id
FOR XML PATH('')), 1, 1, '') AS Result
FROM a t1
GROUP BY id,cgddh
id | cgddh | Result |
123 | OR123456 | 789,456,123 |
456 | OR456789 | 123,456,789 |
SELECT STUFF((
SELECT DISTINCT ',' + CAST(requestid AS VARCHAR)
FROM formtable_main_1007
WHERE cpzy = 377
FOR XML PATH('')), 1, 1, '') AS Result
Result |
123,456,789 |
THE END
0
二维码
打赏
海报
mssql中将查询结果用逗号拼接
业务部门有个需求,付款申请中,采购订单与采购申请是1:N关系,且是多个采购订单一起付款,业务部门要求采购订单付款的时候,需要关联到对应的采购申请流程。……
共有 0 条评论