mysql 高效insert 问题

最新推荐文章于 2023-03-14 17:07:35 发布

net_flyfox

最新推荐文章于 2023-03-14 17:07:35 发布

阅读量1.9k

点赞数

分类专栏：代码讨论文章标签： insert mysql null table join sql

本文链接：https://blog.csdn.net/net_flyfox/article/details/3980731

版权

代码讨论专栏收录该内容

4 篇文章 0 订阅

订阅专栏

前一时间，同事做记账，如果有记录就update ,没有记录就insert 他是用exist做判定。

结果跑了是140s 我后来用union 做了处理结果只有1s左右

视列如下

[code=SQL]

/* 数据表库存帐*/

CREATE TABLE InvBalAccount
(
FiscalPeriod         char(2)               not null /* 会计期间 */,
WareHouseID          int                   not null /* 仓库OID */,
MaterialID           int                   not null /* 货品ID */,
DebQuanAmount        decimal(18,8)         default 0 not null /* 借方数量发生 */,
CredQuanAmount       decimal(18,8)         default 0 not null /* 贷方数量发生 */,
CurQuanBalance       decimal(18,8)         default 0 not null /* 当前数量余额 */,
BatchID              int                   default 0 not null /* 批次ID */,
PurchaseAmount       decimal(18,8)         default 0 not null /* 采购在途数量*/,
SalesAmount          decimal(18,8)         default 0 not null /* 销售在途数量*/,
InitOutQuantity      decimal(18,8)         default 0 not null /* 期初出库数量*/,
constraint InvBalAccount_Key_1 unique (FiscalPeriod, MaterialID, BatchID, WareHouseID) /* Key_1 */
)

/*建临时表*/
CREATE TABLE IF NOT EXISTS T_InvValBalAccount(
T_FiscalPeriod         char(2)               not null /* 会计期间 */,
T_WareHouseID          int                   not null /* 仓库OID */,
T_MaterialID           int                   not null /* 货品ID */,
T_QuanAmount        decimal(18,8)         default 0 not null /* 贷方数量发生 */,
T_ValueAmount       decimal(18,8)         default 0 not null /* 贷方金额发生 */,
T_BatchID              int                   default 0 not null /* 批次ID */,
T_onLoadAmount       decimal(18,8)         default 0 not null /* 销售在途数量*/,
OutType              char(1)               default '0' not null /* 出库方式 0 不使用，1 先进先出 2 后进先出 3 人工选择 */
)
;

/*数据先汇到 T_InvValBalAccount表中，后两个表union 关联实现如下*/

  /*插入不存在的*/
  INSERT INTO InvBalAccount(FiscalPeriod,WareHouseID,MaterialID,BatchID,DebQuanAmount,CurQuanBalance,PurchaseAmount)
  SELECT FiscalPeriod,WareHouseID,MaterialID,BatchID,DebQuanAmount,DebQuanAmount,PurchaseAmount FROM (
  SELECT FiscalPeriod,WareHouseID,MaterialID,BatchID,DebQuanAmount,PurchaseAmount,sum(flag)FROM
  (SELECT FiscalPeriod,WareHouseID,MaterialID,BatchID,DebQuanAmount,PurchaseAmount,1 AS flag
  FROM InvBalAccount INNER JOIN T_invvalbalaccount ON
  InvBalAccount.FiscalPeriod=T_invvalbalaccount.T_FiscalPeriod
  AND InvBalAccount.WareHouseID=T_invvalbalaccount.T_WareHouseID
  AND InvBalAccount.MaterialID=T_invvalbalaccount.T_MaterialID
  AND InvBalAccount.BatchID=T_invvalbalaccount.T_BatchID
  UNION ALL
  SELECT T_FiscalPeriod,T_WareHouseID,T_MaterialID,T_BatchID,T_QuanAmount,T_onLoadAmount,0 AS flag
  FROM T_invvalbalaccount)aa
  GROUP BY FiscalPeriod,WareHouseID,MaterialID,BatchID
  HAVING sum(flag)=0)bb;

[/code]