with mes1 as
(
select c.cugrnname,cdd.cuassociatepackingname,c.cuReceiveTime,c.cuReceivePerson,
CASE WHEN c.custatus = 1 THEN N’已发送‘
ELSE N’已接收‘ END custatus
,c.cuSendTime, c.cuSendPerson,cdd.cupackingworkcenter,cdd.cuqty,pb.PRODUCTNAME,p.description,cdd.description as Reworks ,c.cuconfirmperson as CONFIRM
from cugrn c
inner join cuDetails cd on cd.cugrnid = c.cugrnid
inner join cuassociatepacking cdd on cdd.cuassociatepackingid = cd.cuassociatepackingid
inner join product p on p.productid = cdd.cuproductid
inner join productbase pb on pb.productbaseid = p.productbaseid
where c.cugrnname = N’M221124014′ or cdd.cuassociatepackingname = N’1′
),
mes2 as
(
select th,max(gd) as gd
from
(
select 托号 as th, listagg(工单,’,’) within group ( order by 托号) over (partition by 托号) gd
—–很好的自动拼接某个字段的函数
from
( SELECT C1.CUASSOCIATEPACKINGNAME as 托号,
MO.MFGORDERNAME as 工单
FROM cuAssociatePacking c1
inner join cuAssociateDetails c2 on c1.cuassociatepackingid = c2.cuassociatepackingid
left join cuAssociatePacking C5 on c5.cuassociatepackingname = C2.CUSONLOT
left join cuAssociateDetails c6 on c6.cuassociatepackingid = c5.cuassociatepackingid
left join container C7 on c7.containerid = c6.cufromlotid
LEFT join cucontainergraderrecord ccd on ccd.containername=C7.containername
inner join mfgorder mo on ccd.cuworkorder = mo.mfgordername
inner join mes1 m1 on m1.cuassociatepackingname=c1.CUASSOCIATEPACKINGNAME
where c1.cupackingtype=’Pallet‘ —and c1.cupackingtime>sysdate-1
group by c1.CUASSOCIATEPACKINGNAME,mo.mfgordername
)t
)t1
group by t1.th —-确保一条记录
)
select m1.*,m2.gd
from mes1 m1
left join mes2 m2 on m1.CUASSOCIATEPACKINGNAME=m2.th ；
原文地址:https://blog.csdn.net/prodigywunder/article/details/134722310
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如若转载，请注明出处：http://www.7code.cn/show_18625.html
如若内容造成侵权/违法违规/事实不符，请联系代码007邮箱：suwngjj01@126.com进行投诉反馈，一经查实，立即删除！
