idea
把多行变成一行
Ctrl + Shift + j
sql
- 加字段
alter TABLE `t_user` add wand1 varchar(100) DEFAULT '' COMMENT '辅助编码'
- 数据字典
SELECT t.TABLE_NAME,t.TABLE_COMMENT,c.COLUMN_NAME,c.COLUMN_TYPE,c.COLUMN_COMMENT FROM information_schema.TABLES t,
INFORMATION_SCHEMA.Columns c WHERE c.TABLE_NAME=t.TABLE_NAME AND t.`TABLE_SCHEMA`='g_246_db' ORDER BY t.TABLE_COMMENT
- 关联update
UPDATE pbma a
INNER JOIN ppma b
on a.soco=b.biid
SET a.powh=b.powh
where a.powh is null
- 批量修改 数据
UPDATE `user`
SET sex = (
CASE
WHEN uid = 18 THEN 11
WHEN uid = 19 THEN 22
WHEN uid = 20 THEN 33
END
) WHERE uid IN (18,19,20);
- sql 时间范围
SELECT * FROM actionmessage WHERE crdt > '2021-07-21 00:00:00' and crdt < '2021-07-21 23:59:59' and datagram LIKE "%IM210721URD0293-003%"
- 逗号分隔
select concat(biid,'/',GROUP_CONCAT(inco)) from msde where biid='MV201910110001'
linux 查询
cat catalina.out|grep ‘2018041417434200258001’
-
sql 库存
-- INSERT INTO `stlo`( `biid`, `buty`, `whid`, `btno`, `cotl`, `baco`, `bxfl`, `inco`, `qty`, `whco`, `crus`, `crna`, `crdt`, `rema`, `ismix`, `owco`, `stat`) -- select biid,'tranOut',whid,'default','UR',baco,bxfl,inco,qty*-1,'114',crus,crna,crdt,rema,'1','UR','0' from G_603_wms.pbde where biid='TO202108030982' select * from stlo where biid='TO202108030982' select * from pbde where biid='TO202108030982' update pbma set chus ='URJ200' ,chna='唐红波' , chdt=NOW() where biid='TO202108030982' DELETE from bain where baco='CQR210802URB01185-002' DELETE from slst where biid='TO202108030982'
出库订单主表 obma
出库订单快递信息 bilp
出库订单客户信息 noin
仓库 whco
店铺 shop
物流 lpin
货主 owin
备货计划 ltma CEPxxxx 备货计划明细 ltde
备货任务 ptma CPKxxxx
拣货路径 pkln biid=备货任务biid oubi=来源类型 (出库订单biid)
拣货下架 pkma PDxxxx 拣货下架soco关联备货任务
二次分拣 spma SSxxxx 二次分拣 soco关联备货任务
装箱 pvma PBxxxx (boco箱码,来源单号soco(出库订单,调拨,入库单 ))
装箱明细 pvde 来源单号oubi
装箱复核附属表 obox (biid来源单号)(pkid 拣货下架)
装车交接主表 loma TIxxxx
装车交接明细 lode TIxxxx (obid 来源单号 ,baco 箱码)
出库单 olma STxxxx (soco 来源单号,boco箱码)
出库复核 rema RAxxxx
outOrder | 出库订单 | stockout | 出库单 |
---|---|---|---|
entruckPlan | 备货计划 | pickTask | 备货任务 |
oqc | 出库复核 | otherOut | 其他出库 |
purchasreturn | 采退出库 | pickDown | 拣货下架 |
tranPlan | 调拨计划 | tranOut | 调拨出库 |
tranIn | 调拨入库 | otherIn | 其他入库 |
SALERETURN | 销退入库 | poin | 入库单 |
move | 移库 | whMove | 跨仓移库 |
stockPlan | 盘点计划 | shelf | 补货上架 |
packBox | 装箱 | stockResult | 盘盈盘亏 |
出库订单 | obma | SO | 调拨计划 | ppma | BP/BS/CP/TP |
---|---|---|---|---|---|
备货计划 | ltma | EP | 调拨出库 | pbma | TO |
备货任务 | ptma | PK | 采购退货计划 | pcma | PB |
拣货下架 | pkma | PD | 采购退货出库 | prma | BO |
二次分拣 | spma | SS | 其他出库 | ooma | OO |
出库复核 | rems | RA | 装箱 | pvma | PB |
出库装箱 | obox | PB | 订单快递 | bilp | |
装箱交接 | loma | TI | 库存锁定 | slst | 出库/采退/调拨/移库 |
出库单 | olma | ST |
调拨(BP/BS/CP/CQR/IM/IR/RQR/TF/TO/TFS/YZ)
出库订单生成任务之后 生成备货计划 和备货任务
出库订单obma -> biid 关联 备货计划明细表的 ltde-> oubi
出库订单主表aico连接 备货任务biid
备货计划biid连接备货任务soco
通过备货任务生成拣货下架
锁定表 biid关联 出库订单biid
备货任务 --> 生成拣货路径 备货任务明细可查看拣货路径
出库复核 soco关联出库订单biid
select distinct a.biid,a.soty from ltma a
JOIN ltde b on a.biid=b.biid
left join (select biid from obma where flag<31) c on b.oubi=c.biid
left join (select biid from ppma where flag<21) d on b.oubi=d.biid
left join (select biid from pcma where flag<31) e onb.oubi=e.biid
where 1=1 and a.flag<=11