CREATE PROCEDURE sp_insert_sc_xd_wltgb
@ID int,
@kf varchar(15),
@cpdm varchar(40),
@cplh varchar(40),
@mh varchar(15),
@scxd int,
@gdbh varchar(40),
@gdzq datetime,
@xdfl varchar(6),
@wldm varchar(45),
@wlmc varchar(50),
@wlgg varchar(50),
@lb varchar(10),
@xdyll int,
@rtn int output
AS
declare
@tID int,
@tkf varchar(15),
@tcpdm varchar(40),
@tcplh varchar(40),
@tmh varchar(15),
@tscxd int,
@tgdbh varchar(40),
@tgdzq datetime,
@txdfl varchar(6),
@twldm varchar(45),
@twlmc varchar(50),
@twlgg varchar(50),
@tlb varchar(10),
@txdyll int
SELECT * INTO #TT
FROM
(SELECT a.ID, a.kf, a.cpdm, a.cplh, a.mh,a.scxd,
a.gdbh, a.gdzq, a.xdfl,d.wldm, d.wlmc,
d.wlgg, d.lb, CEILING(ISNULL(a.scxd + 0.00, 1) * ISNULL(d.dwylb, 0)
) AS xdyll
FROM dbo.sc_xd_tgmxb as a INNER JOIN
dbo.BOM_xxb as d ON a.cplh = d.cplh AND a.mh = d.mh WHERE a.ly ='YES')as aa
if exists(SELECT ID, kf,cpdm, cplh, mh,scxd,
gdbh, gdzq, xdfl,wldm, wlmc,
wlgg, lb, xdyll
FROM #TT
where ID=@ID )
BEGIN
SELECT @TID=ID,@tkf=kf,@tcpdm= cpdm,@tcplh= cplh,@tmh=mh,@tscxd=scxd,
@tgdbh=gdbh, @tgdzq=gdzq, @txdfl=xdfl,@twldm=wldm, @twlmc=wlmc,
@twlgg=wlgg, @tlb=lb,@txdyll= xdyll
FROM #TT
where ID=@ID
if ((@TID=@ID) and (@tkf=@kf) and (@tcpdm= @cpdm) and (@tcplh= @cplh) and (@tmh=@mh) and (@tscxd=@scxd)
and (@tgdbh=@gdbh) and (@tgdzq=@gdzq) and (@txdfl=@xdfl) and (@twldm=@wldm) and (@twlmc=@wlmc) and
( @twlgg=@wlgg) and (@tlb=@lb) and (@txdyll= @xdyll))
begin
set @rtn = 0
end
else
begin
update sc_xd_wltgb set ID=@ID,kf=@kf,cpdm= @cpdm,cplh= @cplh,mh=@mh,scxd=@scxd,
gdbh=@gdbh, gdzq=@gdzq, xdfl=@xdfl,wldm=@wldm, wlmc=@wlmc,
wlgg=@wlgg, lb=@lb,xdyll=@xdyll where ID=@ID
set @rtn = 2
end
end
else
begin
insert into sc_xd_wltgb values (@ID, @kf,@cpdm, @cplh, @mh,@scxd, @gdbh, @gdzq, @xdfl,@wldm, @wlmc, @wlgg, @lb, @xdyll)
set @rtn = 1
END
GO
1、有3张表：表1（a.ID, a.kf, a.cpdm, a.cplh, a.mh,a.scxd,a.gdbh, a.gdzq, a.xdfl from dbo.sc_xd_tgmxb as a)下单
表2（d.wldm, d.wlmc, d.wlgg, d.lb,d.dwyl from dbo.BOM_xxb as d )BOM
表3（ID, kf,cpdm, cplh, mh,scxd, gdbh, gdzq, xdfl,wldm, wlmc,wlgg, lb, xdyll from sc_xd_wltgb)物料表
2、通过查询表1，表2 更新数据到表3
遇到问题：
1、表2 BOM 是多款物料对一个表1定单，通过查询会产生多条相同的数据
2、查询无主键，插入新表会产生重得数据
3、做到订单下单时时更新到表3
and (@tgdbh=@gdbh) and (@tgdzq=@gdzq) and (@txdfl=@xdfl) and (@twldm=@wldm) and (@twlmc=@wlmc) and
( @twlgg=@wlgg) and (@tlb=@lb) and (@txdyll= @xdyll))
begin
set @rtn = 0
end
else
begin
update sc_xd_wltgb set ID=@ID,kf=@kf,cpdm= @cpdm,cplh= @cplh,mh=@mh,scxd=@scxd,
gdbh=@gdbh, gdzq=@gdzq, xdfl=@xdfl,wldm=@wldm, wlmc=@wlmc,
wlgg=@wlgg, lb=@lb,xdyll=@xdyll where ID=@ID
set @rtn = 2
end
end
else
begin
insert into sc_xd_wltgb values (@ID, @kf,@cpdm, @cplh, @mh,@scxd, @gdbh, @gdzq, @xdfl,@wldm, @wlmc, @wlgg, @lb, @xdyll)
set @rtn = 1
1、有3张表：表1（a.ID, a.kf, a.cpdm, a.cplh, a.mh,a.scxd,a.gdbh, a.gdzq, a.xdfl from dbo.sc_xd_tgmxb as a)下单
表2（d.wldm, d.wlmc, d.wlgg, d.lb,d.dwyl from dbo.BOM_xxb as d )BOM
表3（ID, kf,cpdm, cplh, mh,scxd, gdbh, gdzq, xdfl,wldm, wlmc,wlgg, lb, xdyll from sc_xd_wltgb)物料表
2、通过查询表1，表2 更新数据到表3
遇到问题：
1、表2 BOM 是多款物料对一个表1定单，通过查询会产生多条相同的数据
2、查询无主键，插入新表会产生重得数据
3、做到订单下单时时更新到表3
大意了 有点复杂 呜呜 没思路
INSERT INTO t_user
SELECT
usera.userid,
usera.name,
usera.department,
usera.position,
usera.mobile,
usera.gender,
usera.email,
usera.weixinid,
usera.enable,
usera.avatar,
usera.status,
usera.creattime,
usera.updatetime,
usera.creatuser FROM
(
SELECT a.*
FROM
t_user_temp a
WHERE NOT EXISTS
( SELECT * FROM t_user_temp c INNER JOIN t_user b ON c.userid = b.userid WHERE a.userid = b.userid )
) usera;
建议 开个悬赏，找人给解决一下
贴完整代码