--先查询
SELECT ip.ISSUE_TYPE,mo.DOC_NO,i.ITEM_CODE,mod.*
FROM [powerjet].[dbo].[MO] mo
left join MO_D mod on mo.MO_ID=mod.MO_ID
left join ITEM i on i.ITEM_BUSINESS_ID=mod.ITEM_ID
left join ITEM_PLANT ip on ip.ITEM_ID=i.ITEM_BUSINESS_ID
where ip.ISSUE_TYPE='2' and mod.ITEM_TYPE='4' and ISSUED_QTY=0 and mo.DOC_DATE between '2022-01-01' and '2022-01-31' and mo.STATUS='Y'
--后更新
update MO_D set ITEM_TYPE=1 FROM [powerjet].[dbo].[MO] mo
left join MO_D mod on mo.MO_ID=mod.MO_ID
left join ITEM i on i.ITEM_BUSINESS_ID=mod.ITEM_ID
left join ITEM_PLANT ip on ip.ITEM_ID=i.ITEM_BUSINESS_ID
----------------------------------------------------------------------------------------------
--先查询
select ip.INBOUND_WAREHOUSE_ID,ip.OUTBOUND_WAREHOUSE_ID,i.ITEM_CODE,i.SHORTCUT,s.fname1,s.fname2,w.WAREHOUSE_CODE,w.WAREHOUSE_ID from ITEM_PLANT ip
left join ITEM i on ip.ITEM_ID=i.ITEM_BUSINESS_ID
inner join Sheet3 s on s.fnumber=i.SHORTCUT
inner join WAREHOUSE w on w.WAREHOUSE_NAME=s.fname2
where ip.ApproveStatus='Y' and ip.INBOUND_WAREHOUSE_ID='BB2B5B80-F08B-4F3A-EEEB-16550989B184'
order by i.ITEM_CODE
--后更新
update ITEM_PLANT set INBOUND_WAREHOUSE_ID = w.WAREHOUSE_ID,OUTBOUND_WAREHOUSE_ID=w.WAREHOUSE_ID from ITEM_PLANT ip
left join ITEM i on ip.ITEM_ID=i.ITEM_BUSINESS_ID
inner join Sheet3 s on s.fnumber=i.SHORTCUT
inner join WAREHOUSE w on w.WAREHOUSE_NAME=s.fname2
where ip.ApproveStatus='Y' and ip.INBOUND_WAREHOUSE_ID='BB2B5B80-F08B-4F3A-EEEB-16550989B184'
order by i.ITEM_CODE
----------------------------------------------------------------------------------------------
--连接外表更新
update BIN set BIN_NAME= bin_temp.BIN_NAME from BIN
inner join bin_temp on bin_temp.BIN_ID=BIN.BIN_ID
--------------------------------------------------------------------------------------------
--连接外表插入
insert into BORROW_DOC_D(
SequenceNumber,
BORROW_DOC_D_ID,
ITEM_DESCRIPTION,
ITEM_SPECIFICATION,
BUSINESS_QTY,
INVENTORY_QTY,
PLAN_RETURN_DATE
,ITEM_ID,
BUSINESS_UNIT_ID,
FROM_WAREHOUSE_ID,
TO_WAREHOUSE_ID,
ApproveStatus,
CreateDate,
CreateBy,
BORROW_DOC_ID
)
select ROW_NUMBER() OVER(ORDER BY i.ITEM_BUSINESS_ID) as 'NUM',
NEWID() as BORROW_DOC_D_ID,
i.ITEM_NAME as ITEM_DESCRIPTION,
i.ITEM_SPECIFICATION ,
jr.[数量] num1,
jr.[数量] num2,
'2021-12-29 00:00:00.0000000' as PLAN_RETURN_DATE,
i.ITEM_BUSINESS_ID as ITEM_ID,u.UNIT_ID,
'DE361061-C064-49AA-17EC-18A90307426F' as FROM_WAREHOUSE_ID,
'5361CDA3-4C92-40FD-E51C-18A34F3B36B5' as TO_WAREHOUSE_ID,
'N' as ApproveStatus,
'2021-12-29 19:10:25.0005532'as CreateDate,
'906A7582-4D15-42AB-B860-183C773F1625' as CreateBy,
'B9B060CE-1505-4059-BF48-18A906812268' as BORROW_DOC_ID
from jieru jr
left join ITEM i on i.ITEM_CODE=jr.[品号]
left join UNIT u on u.UNIT_CODE=jr.[单位]
---------------------------------------------------------------------------------------
一行根据某个字段拆分多行
select id, rwsjjx ,htbh,mbgs
from Prj_TaskProcess pt cross apply Split([hrmid],',') tbl_Ids
where id is not null
SQL在公司常用场景案例分享(二)
于 2023-12-23 09:32:40 首次发布