SQL在公司常用场景案例分享（二）

原创已于 2023-12-23 09:35:37 修改 · 498 阅读

7 ·

CC 4.0 BY-SA版权

文章标签：

#sql #数据库

于 2023-12-23 09:32:40 首次发布

文章涉及SQL查询和更新操作，包括MO表的数据调整、仓库位置变更、BIN表的名称更新以及BORROW_DOC_D表的插入，展示了数据库管理中的常见技术任务。

--先查询
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