2020年12月2日SQL汇总

#根据站点编号查询站点
SELECT * FROM `bs_vending` where siteId='225-0000052'
#查询终端成功请求xx接口的交易记录
select * from sys_trade_log where platCode='03' and tradeCode='1220' and retCode='0000';
select * from sys_trade_log where platCode='03' and tradeCode='1201' and retCode='0000';
#根据站点编号查询入库单信息
select * from bs_inbound where siteId='225-0000052';
#根据站点编号查询设备类型
#1.先根据站点编号查询机型编码
select deviceId from bs_vending where siteId ='225-0000052'
#2.再根据机型编码查询设备类型
select cabinetType from bs_vending_model where deviceId='xx'
#更新入库详情信息表，为卷宗数量字段赋值10
update bs_inbound_info t  set t.archivePageNum = 10
#根据订单编号查询订单信息
select * from bs_borrow_order where order_id='10000158498654916'
#根据站点编号查询寄存主单信息
select * from bs_order where siteId='225-0000056'
#更新寄存主单表,机型是结案柜的数据为archivePageNum字段赋值
update bs_order set archivePageNum=500 where siteName like '%结案%';
#模糊查询
select * from bs_order where siteName like'%结案%'
######################################2020-12-2 14:57:32#######################################################
#根据站点编号、柜子编号、箱子编号查询某个箱子信息
select  logid, slaneId, siteId, siteName, cabinetId, factoryId, deviceId, seqId, boxId,
                boxCode, boxType, bCol, bRow, boxState, curState, stateTime, oderId, createTime,
                corpId,userId,drawerNum,boxCy  from bs_vending_lane
                        where siteId='225-0000056' and cabinetId='1' and slaneId='225-0002844'
                        
#根据站点编号、箱子编号连接查询某个箱子信息和箱子对应的archivePageNum(箱子已经存放卷宗页数)                
select  vl.logid, vl.slaneId, vl.siteId, vl.siteName, vl.cabinetId, vl.factoryId, vl.deviceId, vl.seqId, vl.boxId,
                vl.boxCode, vl.boxType, vl.bCol, vl.bRow, vl.boxState, vl.curState, vl.stateTime, vl.oderId, vl.createTime,
                vl.corpId,vl.userId,vl.drawerNum,vl.boxCy,o.archivePageNum from bs_vending_lane as vl
                        left join bs_order as o on vl.siteId=o.siteId and vl.slaneId=o.boxId
                        where vl.siteId='225-0000056' and vl.cabinetId='1' and vl.slaneId='225-0002844'