由Delphi程式改寫Sql(2000)的儲存過程
各位前輩大家好,請幫我解決一個棘手的問題,我不會寫SQL儲存過程,現在一個Delphi程式執行非常的慢,想將其用SQL儲存過程替代
請各位前輩幫忙,線上等
主要 資料表單有 MRPplan,MRPplantou (生產單資料表), mrpplanpsn(物料需求單資料表),bom(BOM 資料表) 四個，
其中關聯欄位為Psn(生產單號),materysn(物料編號)
從 MRPplan 中依據Psn查詢資料進行迴圈,
a:假如mrpplanpsn 中有對應Psn的materysn則將MRPplan.planshu數量加到 mrpplanpsn.gr上
b:假如mrpplanpsn 中沒有有對應Psn的materysn則將MRPplan中相關資料寫入 mrpplanpsn
c:進行對MRPplan.materysn 資料進行BOM分解，若有子項則對子項進行以上動作
1: Select psn.materysn,planshu from MRPplan where psn=xxx (xxx 為傳入引數),對應資料集為rsq1
If rsq1.RecordCount> 0 then
Begin
For I:=1 To rsq1.RecordCount Do
Begin
fstr:=rsq1.materysn
Select * From mrpplanpsn where materysn=fstr and psn=xxx (xxx 為傳入引數),對應資料集為rst1
If rst1.RecordCount> 0 Then 將對應 rst1.gr=rst1.gr rsq1.planshu
Else
Begin
rst1.Append //新寫入對應內容
rst1.materysn=rsq1.materysn
rst1.gr=rsq1.planshu //數量
rst1.consigndate=rsq1.consigndate //交獲日期
...
End;
進行BOM資料對應查詢，rsq2.SQL.Add('select materysn from bom where fmaterysn=fstr）
If Rsq2.RecordCount Then SGmaterysn(Fstr;rsq1.planshu) 進行迴圈
Rsq2.Next
End //For I:=1 To rsq1.RecordCount Do
End;
SGmaterysn 過程如下:
select materysn,useshu from bom where fmaterysn=Fstr and useshu> 0
n:=Rsq2.RecordCount
If n > 0 Then
Begin
For i:= 1 To n do
begin
Fstr=Rsq2.Materysn
planshu:=Rsq2.Materysn
select materysn,gr,fu,psn,consigndate,remark from mrpplanpsn where materysn=Rsq2.Materysn and
psn=xxx
If rst2.RecordCount > 0 Then 則將對應 rst2.gr=rst2.gr rsq2.planshu
Else
Begin
rst2.Append //新寫入對應內容
rst2.materysn=rsq2.materysn
rst2.gr=rsq2.planshu
rst2.consigndate=rsq2.consigndate //交獲日期
...
End;
進行BOM資料對應查詢，rsq2.SQL.Add('select materysn from bom where fmaterysn=fstr）
If Rsq2.RecordCount > 0 Then SGmaterysn(Fstr;planshu) 進行遞迴迴圈
SGmaterysn(Fstr;planshu);
rsq2.Next ;
end; //For i:= 1 To n do
end;//If n > 0 Then
網友回覆:看著真累
網友回覆:其實看看前面一小段就大概明白了,後面是虛擬碼,加強說明的.麻煩大哥看看,幫幫忙.謝謝
網友回覆:請高手支招啊.看看前面幾行就大概明白了
網友回覆:1.BOM表中有個欄位是 fmaterysn ? 記錄父級materysn？
2.MRPplan,MRPplantou 這兩個表什麼關係？
網友回覆:大體寫了一個流程
主要的思路是先迴圈出所有的materysn
再更新，而不是找一層，更新一層
MRPplantou 表沒有用上，我估計可能MRPplan,MRPplantou 兩個表要關聯起來使用吧？
這個你自己改改就行
SQL code
Code highlighting produced by Actipro CodeHighlighter (freeware)
create proc p_1
@Psn varchar(100) --改成你自己的型別
as
begin
--建立臨時表(跟你的MRPplan表結構一樣就行)
declare @MRPplan table
(
Psn varchar(100),
materysn varchar(100),
planshu int,
....
)
--根據psn匯入資料
insert into @MRPplan(...)
select *
from MRPplan
where Psn = '你的引數'
--迴圈匯入子項
while (@@rowcount > 0)
begin
insert into @MRPplan(...)
select a.*
from MRPplan a inner join bom b on a.materysn = b.materysn
inner join @MRPplan c on b.fmaterysn = c.materysn
where a.materysn not in (select distinct materysn from @MRPplan)
end
--有對應的話就更新
update a
set a.gr = a.gr b.planshu
from mrpplanpsn a inner join @MRPplan b
on a.psn = b.psn and a.materysn = b.materysn
--沒有對應的話就插入
insert into mrpplanpsn(....)
select ...
from @MRPplan a left join mrpplanpsn b
on a.psn = b.psn and a.materysn = b.materysn
where b.materysn is null
end
***********************************************
樓主假如看了上面的思路還是沒法下手的話，建議樓主把四個表的結構貼出來，
每個表再抓20條資料出來，然後告訴大家你想要的結果，以及其中的演算法
這樣的話馬上就會有人給你答案的
網友回覆:我先測試看看 真是感激不盡
網友回覆:仍然沒有解決 期待幫忙 謝謝
資料表結構如下:
MRPplantou 生產單表頭
psn varchar(20) NOT NULL 生產單號
rev smallint(2) NOT NULL 版本號
makeren varchar(20) NOT NULL 制單人
makedate datetime(8) NOT NULL 制單日期
sureren varchar(20) NOT NULL 稽核人
suredate datetime(8) NOT NULL 稽核日期
state int(4) NOT NULL 稽核狀態 (0:未稽核;1:已刪除;2:已稽核;3:已算毛需求;4:已算可用量;5:已算淨需求 6:淨需求已稽核 8:
已結單 )
psn 20080104 生產單號
MRPplan 生產單表身
line smallint(2) NOT NULL 項次
psn varchar(20) NOT NULL 生產單號
materysn varchar(30) NOT NULL 物料編號
planshu numeric NOT NULL 批次 生產數量
consigndate smalldatetime(4) NOT NULL 交貨日期
remarks varchar(50) NULL 備註
Psn 20080104 (生產單號)
materysn 38912 (產品編號)
planshu 100 (生產數量)
BOM 基本資訊
fmaterysn char (30) 貨號編號
materysn char (30) 物料編號
son char (30)
useshu real (4) 用量
unit varchar (10) 單位
fmaterysn materysn useshu unit
38912 38912-1 10 pcs
38912-1 38912-1-1 10 pcs
38912 38912-2 10 pcs
38912-2 38912-2-1 10 pcs
38912-1 38912-1-2 10 pcs
mrpplanpsn MRP需求計算檔
psn varchar(20) NOT NULL 生產單號
materysn char (30) 物料遍號
son char (1)
gr numeric(9) NOT NULL 毛需求 (初始植為MRPPlan的Planshu 批次數 )
fu numeric(9) NULL 父階沖銷
stocks numeric(9) NULL 在庫量
sastocks numeric(9) NULL 安全庫存量
sr numeric(9) NULL 在途量
al numeric(9) NULL 保留量
ky numeric(9) NULL 可用量
nr numeric(9) NULL 淨需求
consigndate datetime NOT NULL 需求日期
remark varchar (200) 備註
網友回覆:我參照寫了一些 但是執行很慢 等了很久很久不見結果 ,請幫我看看
use yw
go
if exists(select name from sysobjects where name ='bom_level_ext1' and type='p') --判定是否存在該過程,存
在則刪除
drop proc dbo.bom_level_ext1
go
create procedure dbo.bom_level_ext1 --建立過程
@tmppsn varchar(20) --傳入引數
--with encryption
as --建立變數
declare @min_no varchar(20)
declare @rows_count integer
select @min_no = ''
create table #t_psn(psn varchar(20), materysn varchar(50) null,planshu float,consigndate
datetime,flag char(1) )
create index idex1 on #t_psn(materysn)
insert into #t_psn (psn, materysn,planshu,consigndate,flag)
select psn,materysn,isnull(planshu,0),consigndate ,'N' from MRPplan where psn=@tmppsn --傳入PSN 合同
編號
select @rows_count = count(#t_psn.materysn) from #t_psn --查詢#t_psn 資料筆數
if (@rows_count > 0 )
begin
select @min_no =min(#t_psn.materysn) from #t_psn where #t_psn.flag = 'N'
while not(@min_no is null or @min_no ='')
begin
--插入臨時表格資料
insert into #t_psn(materysn,planshu,flag) --對BOM查詢 看是否有子項
select dbo.bom.materysn,isnull(dbo.bom.useshu,0),'N'
from dbo.bom
where dbo.bom.fmaterysn = @min_no
******************************************
end
update #t_psn set #t_psn.flag = 'Y' where #t_psn.materysn = @min_no and #t_psn.flag='N'
select @min_no =min( #t_psn.materysn) from #t_psn where flag = 'N'
end
-- drop table #t_pno
select * from #t_psn order by materysn
--select * from #t_materysn
go
--grant all on dbo.bom_level_ext to users --賦權
--go
網友回覆:--這裡插入臨時表的flag列的資料都是'N'
insert into #t_psn (psn, materysn,planshu,consigndate,flag)
select psn,materysn,isnull(planshu,0),consigndate ,'N'
from MRPplan
where psn=@tmppsn --傳入PSN 合同編號
--這裡判定有沒有資料沒有錯，不過可以改為判定@@rowcount
select @rows_count = count(#t_psn.materysn) from #t_psn --查詢#t_psn 資料筆數
if (@rows_count > 0 )
begin
select @min_no =min(#t_psn.materysn) from #t_psn where #t_psn.flag = 'N'
--這裡判定是也是記錄數吧？上面不是已經判定了嗎？為什麼要再一次判定？
--而且為什麼要加條件呢，上面你插入的資料，只要有資料，就是'N'了
--這個while是個死迴圈吧，假如條件滿足的話，在迴圈體中間並沒有重新對變數進行賦值，所以會一直執行下去 while not(@min_no is
null or @min_no ='')
begin
--插入臨時表格資料
insert into #t_psn(materysn,planshu,flag) --對BOM查詢 看是否有子項
select dbo.bom.materysn,isnull(dbo.bom.useshu,0),'N'
from dbo.bom
where dbo.bom.fmaterysn = @min_no
end
update #t_psn set #t_psn.flag = 'Y' where #t_psn.materysn = @min_no and #t_psn.flag='N'
select @min_no =min( #t_psn.materysn) from #t_psn where flag = 'N'
end
網友回覆:我的本來意思是第一次插入 狀態標誌 Flag='N',滿足條件後將Flag='Y'
迴圈判定 while 中的引數 @min_no 前面已經賦值
網友回覆:--這兩句似乎也有點問題，@min_no 是查詢出的某個materysn值吧，樓主為什麼要對每個materysn逐條處理呢？
這個不能放在一起進行處理？
沒看懂業務，呵呵，猜得
update #t_psn set #t_psn.flag = 'Y' where #t_psn.materysn = @min_no and #t_psn.flag='N'
select @min_no =min( #t_psn.materysn) from #t_psn where flag = 'N'
網友回覆:迴圈判定while中的引數@min_no前面已經賦值
對阿，就是因為前面已經賦值了
所以比如@min_no = '112233',那麼它不是null，也不為空（''）
然後就執行while裡的語句
執行完了以後一看，@min_no 值還是 '112233'，
然後就又執行一次。。。
這不就是死迴圈嘛?
網友回覆:下班了，明天來關注！ ：）
網友回覆:要已manterysn 作為條件去BOM 中查詢是否有子項 有的話繼續迴圈
網友回覆:也就是要將 MRPplan.materysn 的產品38912 進行分解,分解依據是產品的BOM表, 計算出每個物料的用量,將其結果寫入
mrpplanpsn 物料需求計算表格
網友回覆:要已manterysn 作為條件去BOM 中查詢是否有子項 有的話繼續迴圈
--那這樣的思路有問題嗎？當然可能關聯條件是有問題的，具體的要樓主自己分析
--迴圈匯入子項
while (@@rowcount > 0)
begin
insert into @MRPplan(...)
select a.*
from MRPplan a inner join bom b on a.materysn = b.materysn
inner join @MRPplan c on b.fmaterysn = c.materysn
where a.materysn not in (select distinct materysn from @MRPplan)
end
樓主說這個思路不行，也要說出什麼地方不行來吧 。。。。
網友回覆:也就是要將 MRPplan.materysn 的產品38912 進行分解,分解依據是產品的BOM表, 計算出每個物料的用量,將其結果寫入
mrpplanpsn 物料需求計算表格
************************************
過程是不是這樣的？？？
select * from bom where fmaterysn = '38912'
比如結果集中有兩種物料
3891201 3891202
那就判定mrpplanpsn 表中有沒有，有就更新，沒有就插入
然後再看看3891201 3891202在bom表中有沒有子物料，有的話再重複
網友回覆:是的,的確是這樣.一直迴圈到 BOM 中沒有相關資料為止
網友回覆:冷簫輕笛 有在嗎? 繼續請教問題.昨天沒有上班
網友回覆:我略懂DELPHI,乾脆你要做什麼，直接給出來，給出表結構就OK
網友回覆:資料表結構如下:
我要做的事情就是要將MRPplan 中的產品依據BOM進行分解,然後將其資料與 mrpplanpsn (物料需求表)進行比對,假如有相同條件資料就對數
量想加 否則就新增. 比對條件是 PSN,Materysn 兩個要害字,MRPplan跟BOMDE的關聯欄位為materysn,fmaterysn.
MRPplantou 生產單表頭
psn varchar(20) NOT NULL 生產單號
rev smallint(2) NOT NULL 版本號
makeren varchar(20) NOT NULL 制單人
makedate datetime(8) NOT NULL 制單日期
sureren varchar(20) NOT NULL 稽核人
suredate datetime(8) NOT NULL 稽核日期
state int(4) NOT NULL 稽核狀態 (0:未稽核;1:已刪除;2:已稽核;3:已算毛需求;4:已算可用量;5:已算淨需求 6:淨需求已稽核 8:
已結單 )
psn 20080104 生產單號
MRPplan 生產單表身
line smallint(2) NOT NULL 項次
psn varchar(20) NOT NULL 生產單號
materysn varchar(30) NOT NULL 物料編號
planshu numeric NOT NULL 批次 生產數量
consigndate smalldatetime(4) NOT NULL 交貨日期
remarks varchar(50) NULL 備註
Psn 20080104 (生產單號)
materysn 38912 (產品編號)
planshu 100 (生產數量)
BOM 基本資訊
fmaterysn char (30) 貨號編號
materysn char (30) 物料編號
son char (30)
useshu real (4) 用量
unit varchar (10) 單位
fmaterysn materysn useshu unit
38912 38912-1 10 pcs
38912-1 38912-1-1 10 pcs
38912 38912-2 10 pcs
38912-2 38912-2-1 10 pcs
38912-1 38912-1-2 10 pcs
mrpplanpsn MRP需求計算檔
psn varchar(20) NOT NULL 生產單號
materysn char (30) 物料遍號
son char (1)
gr numeric(9) NOT NULL 毛需求 (初始植為MRPPlan的Planshu 批次數 )
fu numeric(9) NULL 父階沖銷
stocks numeric(9) NULL 在庫量
sastocks numeric(9) NULL 安全庫存量
sr numeric(9) NULL 在途量
al numeric(9) NULL 保留量
**************************************
ky numeric(9) NULL 可用量
nr numeric(9) NULL 淨需求
consigndate datetime NOT NULL 需求日期
remark varchar (200) 備註
網友回覆:假如從上面看下來,能基本瞭解到我的意圖.
假如仁兄肯幫忙,欲看Delphi 的原始碼,我可以發給你,
謝謝
網友回覆:產品依據BOM進行分解
===================
這是什麼意思？
網友回覆:來了
網友回覆:MRPplantou表 是不是沒有用阿？
因為你需要的流程是根據psn生產單號查詢planshu，然後更新mrpplanpsn表
對吧？
網友回覆:bom表中的三個欄位
fmaterysn char (30) 貨號編號
materysn char (30) 物料編號
son char (30)
哪兩個是用來標示父子關係的阿？
網友回覆:fmaterysn(父親) materysn(兒子) useshu(數量) unit(單位)
38912 38912-1 10 pcs
38912-1 38912-1-1 10 pcs
38912 38912-2 10 pcs
38912-2 38912-2-1 10 pcs
38912-1 38912-1-2 10 pcs
產品38912
1:將產品編號38912,PSN=xxx 至 之mrpplanpsn MRP需求計算檔查詢,沒有相關資料 則將將產品編號38912,PSN=xxx 寫入
mrpplanpsn中
2:對38912 至BOM 中查詢 是否有兒子,有 38912-1,38912-2 兩個,分別將其PSN=xxx至mrpplanpsn中比,假如有則進行數量想加否則
新增
3:對38912-1,38912-2至BOM中查詢....
網友回覆:MRPplantou表 該處可以不考慮,是執行完後將MRPplantou.state 狀態標誌改寫 為已經算料的狀態的意思
網友回覆:
SQL code
Code highlighting produced by Actipro CodeHighlighter (freeware)
--把上面的修改了一下，樓主看看還有什麼地方不妥
--有地方需要樓主自己改，比如找不到的話，就要插入到mrpplanpsn，但是mrpplanpsn表的列有好多阿。。。
--思路跟樓主的有點不一樣:我是先找產品編號38912，然後接著就找他的下級38912-1,38912-2，
--迴圈取到最低階，再進行資料處理。這樣的效率肯定會更好，但是不知道會不會符合樓主的需求
create proc P_UpdateMrpplanpsn
@Psn varchar(100) --改成你自己的型別
as
begin
--建立臨時表
declare @MRPplan table
(
Psn varchar(100),
materysn varchar(100),
planshu int
)
--根據psn匯入一級資料
insert into @MRPplan(psn,materysn,planshu)
select psn,materysn,planshu
from MRPplan
where Psn = @Psn
--迴圈匯入子項
while (@@rowcount > 0)
begin
insert into @MRPplan(psn,materysn,planshu)
select a.psn,a.materysn,a.planshu
from MRPplan a inner join bom b on a.materysn = b.materysn
inner join @MRPplan c on b.fmaterysn = c.materysn
where not exists (select 1 from @MRPplan where psn = a.psn and materysn = a.materysn)
end
--有對應的話就更新
update a
set a.gr = a.gr b.planshu
from mrpplanpsn a inner join @MRPplan b
on a.psn = b.psn and a.materysn = b.materysn
--沒有對應的話就插入
--這個就看你具體的需求了,因為mrpplanpsn表還有其他的列，這裡並不知道取值方法
insert into mrpplanpsn(psn,materysn,gr)
select a.psn,a.materysn ,a.planshu
from @MRPplan a left join mrpplanpsn b
on a.psn = b.psn and a.materysn = b.materysn
where b.materysn is null
end
網友回覆:先測試看看,真心感謝 冷簫輕笛.
網友回覆:@@rowcount 怎麼沒有賦值啊,他應該是誰的值
網友回覆:@@rowcount 是系統變數，記錄的是上一條sql語句執行後對資料庫影響的行數
網友回覆:MRPplan中 的materysn 可能只有2筆
但經過BOM分解後可能有50筆 ,所以 marplan.materysn=bom.materysn 應該是不對的
比如Mrpplan 只有38912 一筆
而由此產品 38912 分解出來的物料則有 38912,38912-1,38912-2,38912-1-1,38912-1-2,38912-2-1,38912-2-2
等等.
網友回覆:對阿，分解以後確實可能有很多筆
但是樓主別忘了，marplan.materysn=bom.materysn是寫在迴圈裡的
38912是在迴圈的上一步查到的，
迴圈中第一次查的是38912-1,38912-2
第二次查的是38912-1-1,38912-1-2,38912-2-1,38912-2-2
。。。。
為什麼會有問題呢？
網友回覆:結果是寫到@MRPplan 中吧,我最後用了一條 select * from @MRPplan 語句
我測試了,結果不對 但是不知原因
***********************************
===========================
他的子項38912-1,38912-2,38912-1-1,38912-1-2,38912-2-1,38912-2-2
在Mrpplan表裡沒有？？？？？？
網友回覆:是的,Mrpplan中只有38912一筆,但該產品進行分解在BOM中是有多筆的
網友回覆:少什麼記錄？
樓主可以這樣除錯：
注重：一定要一步一步的執行，每執行一步就檢視資料是否正確
這樣可以查詢出問題出在什麼地方了
SQL code
Code highlighting produced by Actipro CodeHighlighter (freeware)
--1.表變數改成臨時表
create table #MRPplan
(
Psn varchar(100),
materysn varchar(100),
planshu int,
)
--2.根據psn匯入資料
insert into #MRPplan(psn,materysn,planshu)
select psn,materysn,planshu
from MRPplan
where Psn = '你的引數'
--3.查詢資料,看看資料是否正確，假如正確就執行第4步
select * from #MRPplan
--4.匯入子項
insert into #MRPplan(psn,materysn,planshu)
select a.psn,a.materysn,a.planshu
from MRPplan a inner join bom b on a.materysn = b.materysn
inner join #MRPplan c on b.fmaterysn = c.materysn
where not exists (select 1 from #MRPplan where psn = a.psn and materysn = a.materysn)
--5.執行第3步
網友回覆:是的,Mrpplan中只有38912一筆,但該產品進行分解在BOM中是有多筆的
----------------------------
哦，這樣啊
那你已開始說的是：
a:假如mrpplanpsn 中有對應Psn的materysn則將MRPplan.planshu數量加到 mrpplanpsn.gr上
b:假如mrpplanpsn 中沒有有對應Psn的materysn則將MRPplan中相關資料寫入 mrpplanpsn
c:進行對MRPplan.materysn 資料進行BOM分解，若有子項則對子項進行以上動作
這樣的話c步驟中的子項的操作跟父項其實是不一樣的阿！
網友回覆:請問如何單步執行.謝謝
網友回覆:本來要用MRPplan.planshu欄位來更新mrpplanpsn.gr
但是子項沒有planshu，要用哪個欄位來更新阿？
網友回覆:--你是在查詢分析器裡測試吧？
選中你要執行的步驟語句，然後再點執行，不要直接點執行或者全選以後執行。
網友回覆:Bom 中的數量是useshu real (4) 用量
請問如何單步執行.謝謝
網友回覆:我指是一條一條語句執行
網友回覆:那子項中的psn是否也是取的開始傳入的@Psn變數呢？
假如是的話，把迴圈改成這樣：
SQL code
Code highlighting produced by Actipro CodeHighlighter (freeware)
--迴圈匯入子項
while (@@rowcount > 0)
begin
insert into @MRPplan(psn,materysn,planshu)
select @Psn,a.materysn,a.useshu
from bom a inner join @MRPplan b on a.fmaterysn = b.materysn
where not exists (select 1 from @MRPplan where psn = @Psn and materysn = a.materysn)
end
網友回覆:先把迴圈改了以後測試一下吧，不行的話再單步測試
網友回覆:
請問如何單步執行.謝謝
我指是一條一條語句執行
你指的是除錯程式那種單步執行？
這個sqlserver可不支援，呵呵，就得手動一條一條的執行
網友回覆:冷簫輕笛 您好:
1: 計算出來的結果是對的，但是有以下兩個問題需要解決。懇請繼續幫忙。不勝感激
2：結果表中的@MRPplan的materysn 為唯一值
也就是說假如有相同的要進行數量的累加.如下 mrpplanpsn.gr=mrpplanpsn.gr 分解該Bom的父親數量 * bom.useshu 即為
mrpplanpsn.gr=10(mrpplanpsn.gr 之前的) 傳入的materysn*bom.useshu
3：需要寫入日期 mrpplay.consigndate日期
網友回覆:1.不客氣
2.“分解該Bom的父親數量”
這個值是如何取出來的呢？
3.日期好辦，語句的最前面用
declare @date datetime
select @date = consigndate from mrpplan where psn = @psn
就可以了
然後表變數加上這列，插入資料的時候直接插入變數@date就可以了
網友回覆:哦，有點明白了，是不是這個意思？
比如：
38912-1,38912-2
子項38912-1-1,38912-1-2,38912-2-1,38912-2-2
假如38912-1的useshu = 10
那麼計算38912-1-1 的時候要用10 * useshu ？然後加在mrpplanpsn.gr上，而不是直接加useshu？
網友回覆:但是這句又不懂了。。。
即為 mrpplanpsn.gr=10(mrpplanpsn.gr 之前的) 傳入的materysn * bom.useshu
網友回覆:哦，consigndate 是mrpplan表裡的？那同一個psn裡不同的物料交貨時間不同？
假如這樣的話，那子項的consigndate如何來確定？
網友回覆:比如 A 是 A1,A2的父親 ,在BOM 中 為
fmaterysn materysn, useshu
A A 10
A A1 5
A A2 8
而且
mrpplanpsn 中已經有
psn materysn gr, consigndate
20080104 A1 20 2008/01/04
則下一個動作是進行gr累計
mrpplanpsn.gr=mrpplanpsn.gr 10*5
mrpplanpsn.gr=10 10*5
簡單的就是說 要做10個桌子,(1個桌子假如要4個腳) 則總腳數為 10 *4 =40
****************************************##########
然後再加其他產品需要這個腳的數目就是該定單或一些產品需要總腳的數目
總腳資料:=之前腳資料 10*40
網友回覆:
SQL code
Code highlighting produced by Actipro CodeHighlighter (freeware)
--再修改
create proc P_UpdateMrpplanpsn
@Psn varchar(100) --改成你自己的型別
as
begin
--建立臨時表
declare @MRPplan table
(
Psn varchar(100),
materysn varchar(100),
planshu int,
f_planshu int,
cdate datetime
)
--獲取consigndate
declare distinct @date = consigndate from mrpplay where psn = @psn
--根據psn匯入一級資料
insert into @MRPplan(psn,materysn,planshu,f_planshu,cdate)
select psn,materysn,planshu,1,@date
from MRPplan
where Psn = @Psn
--迴圈匯入子項
while (@@rowcount > 0)
begin
insert into @MRPplan(psn,materysn,planshu,f_planshu,cdate)
select @Psn,a.materysn,a.useshu,b.planshu,@date
from bom a inner join @MRPplan b on a.fmaterysn = b.materysn
where not exists (select 1 from @MRPplan where psn = @Psn and materysn = a.materysn)
end
--有對應的話就更新
update a
set a.gr = a.gr b.planshu * b.f_planshu
from mrpplanpsn a inner join @MRPplan b
on a.psn = b.psn and a.materysn = b.materysn
--沒有對應的話就插入
--這個就看你具體的需求了,因為mrpplanpsn表還有其他的列，這裡並不知道取值方法
insert into mrpplanpsn(psn,materysn,gr)
select a.psn,a.materysn ,a.planshu * a.f_planshu
from @MRPplan a left join mrpplanpsn b
on a.psn = b.psn and a.materysn = b.materysn
where b.materysn is null
end
網友回覆:fmaterysn materysn, useshu
A A 10
A A1 5
A A2 8
A1 A11 12
那麼計算A11的時候是否是 (5*10)*12 ?
假如是的話，上面的語句沒有問題，呵呵
網友回覆:哦，consigndate 是mrpplan表裡的？那同一個psn裡不同的物料交貨時間不同？
假如這樣的話，那子項的consigndate如何來確定？
網友回覆:過30了沒有？
網友回覆:consigndate 一個個固定的值 ,是mrpplan表裡的
網友回覆:哈哈,過了點點
網友回覆:上面的sql有問題，修改中
網友回覆:
SQL code
Code highlighting produced by Actipro CodeHighlighter (freeware)
--樓主，你給的資料庫俺用不了，公司的許可權管的嚴，呵呵，自己建了三個表，測試一下，樓主看看
--環境
create table mrpplan
(
psn varchar(20),
materysn varchar(30),
planshu numeric,
consigndate smalldatetime
)
insert into mrpplan(psn,materysn,planshu,consigndate)
select '20080104','38912',100,'2008-02-01'
create table bom
(
fmaterysn char (30), -- 貨號編號(父親)
materysn char (30), --物料編號(兒子)
useshu real , --用量
unit varchar (10) --單位
)
insert into bom(fmaterysn,materysn,useshu,unit) select '38912', '38912-1' , 10, 'pcs'
insert into bom(fmaterysn,materysn,useshu,unit) select '38912-1', '38912-1-1', 10, 'pcs'
insert into bom(fmaterysn,materysn,useshu,unit) select '38912', '38912-2' , 10, 'pcs'
insert into bom(fmaterysn,materysn,useshu,unit) select '38912-2', '38912-2-1', 10, 'pcs'
insert into bom(fmaterysn,materysn,useshu,unit) select '38912-1', '38912-1-2', 10, 'pcs'
CREATE TABLE [dbo].[MRPplanpsn] (
[psn] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[materysn] [char] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[son] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
[gr] [numeric](14, 4) NULL ,
[fu] [numeric](14, 4) NULL ,
[stocks] [numeric](14, 4) NULL ,
[sastocks] [numeric](14, 4) NULL ,
[sr] [numeric](14, 4) NULL ,
[al] [numeric](14, 4) NULL ,
[ky] [numeric](14, 4) NULL ,
[nr] [numeric](14, 4) NULL ,
[consigndate] [datetime] NULL ,
[remark] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
--建立儲存過程
create proc P_UpdateMrpplanpsn
@Psn varchar(100) --改成你自己的型別
as
begin
--建立臨時表
declare @MRPplan table
(
Psn varchar(100),
materysn varchar(100),
planshu int,
f_planshu int,
cdate datetime
)
--根據psn匯入一級資料
insert into @MRPplan(psn,materysn,planshu,f_planshu,cdate)
select psn,materysn,planshu,1,consigndate
from MRPplan
where Psn = @Psn
--迴圈匯入子項
while (@@rowcount > 0)
begin
insert into @MRPplan(psn,materysn,planshu,f_planshu,cdate)
select @Psn,a.materysn,a.useshu,b.planshu * b.f_planshu,b.cdate
from bom a inner join @MRPplan b on a.fmaterysn = b.materysn
where not exists (select 1 from @MRPplan where psn = @Psn and materysn = a.materysn)
end
--有對應的話就更新
update a
set a.gr = a.gr b.planshu * b.f_planshu
from mrpplanpsn a inner join @MRPplan b
on a.psn = b.psn and a.materysn = b.materysn
--沒有對應的話就插入
--這個就看你具體的需求了,因為mrpplanpsn表還有其他的列，這裡並不知道取值方法
insert into mrpplanpsn(psn,materysn,gr)
select a.psn,a.materysn ,a.planshu * a.f_planshu
from @MRPplan a left join mrpplanpsn b
on a.psn = b.psn and a.materysn = b.materysn
where b.materysn is null
end
--執行
exec P_UpdateMrpplanpsn '20080104'
--結果
psn materysn gr
-------------------- ------------------------------ ----------------
20080104 38912 100.0000
20080104 38912-1 1000.0000
20080104 38912-2 1000.0000
20080104 38912-1-1 10000.0000
20080104 38912-1-2 10000.0000
20080104 38912-2-1 10000.0000
（所影響的行數為 6 行）
--下班了，再有問題只好等下個周了，呵呵
**************************************************
網友回覆:冷簫輕笛 正解......
網友回覆:其中更新部分(如下)沒有執行任何動作
--有對應的話就更新
update a
set a.gr = a.gr b.planshu * b.f_planshu
from mrpplanpsn a inner join @MRPplan b
on a.psn = b.psn and a.materysn = b.materysn
以上程式碼疲敝與否結果是一樣的,也就是說 寫入 mrpplanpsn 中的資料與@MRPplan中的資料筆數是一樣的.
我測試了您以上資料,但是假如加入調整資料時就有問題
假如某產品(如產品 389122的產品結構對應的BOM)進行分解有對應 如下資料
insert into bom(fmaterysn,materysn,useshu,unit) select '38912-1', '38912-1-1', 20, 'pcs'
則計算出來資料是 7 筆 而非 6筆
--結果
psn materysn gr
-------------------- ------------------------------ ----------------
20080104 38912 100.0000
20080104 38912-1 1000.0000
20080104 38912-2 1000.0000
20080104 38912-1-1 10000.0000
20080104 38912-1-2 10000.0000
20080104 38912-2-1 10000.0000
20080104 38912-1-1 20000.0000
正確結果應為
--結果
psn materysn gr
-------------------- ------------------------------ ----------------
20080104 38912 100.0000
20080104 38912-1 1000.0000
20080104 38912-2 1000.0000
20080104 38912-1-1 30000.0000
20080104 38912-1-2 10000.0000
20080104 38912-2-1 10000.0000
望解
網友回覆:其中更新部分(如下)沒有執行任何動作
--有對應的話就更新
update a
set a.gr = a.gr b.planshu * b.f_planshu
from mrpplanpsn a inner join @MRPplan b
on a.psn = b.psn and a.materysn = b.materysn
以上程式碼疲敝與否結果是一樣的,也就是說 寫入 mrpplanpsn 中的資料與@MRPplan中的資料筆數是一樣的.
-----------------------------------------------
這個你是用我的資料測試的？
那肯定是有沒有都一樣啊，我的測試環境本來裡面就滅有資料，當然只做插入不做更新阿
假如不是，請說明什麼情況下的什麼資料不能更新
==================================================================
你的bom表答應這樣的紀錄同時存在？？？？
'38912-1', '38912-1-1', 10, 'pcs'
'38912-1', '38912-1-1', 20, 'pcs'
假如是的話，那跟我理解的bom表是不一樣的。
SQL code
Code highlighting produced by Actipro CodeHighlighter (freeware)
--迴圈匯入子項
while (@@rowcount > 0)
begin
insert into @MRPplan(psn,materysn,planshu,f_planshu,cdate)
select @Psn,a.materysn,a.useshu,b.planshu * b.f_planshu,b.cdate
from bom a inner join @MRPplan b on a.fmaterysn = b.materysn
where not exists (select 1 from @MRPplan where psn = @Psn and materysn = a.materysn)
end
--改成這個試試：
--迴圈匯入子項
while (@@rowcount > 0)
begin
insert into @MRPplan(psn,materysn,planshu,f_planshu,cdate)
select @Psn,a.materysn,sum(a.useshu),sum(b.planshu * b.f_planshu) ,b.cdate
from bom a inner join @MRPplan b on a.fmaterysn = b.materysn
where not exists (select 1 from @MRPplan where psn = @Psn and materysn = a.materysn)
group by a.materysn,b.cdate
end
****************************************************************
網友回覆:--好累，蓋了六十多樓了，問題還沒有敘述明白。。。。 -_-!!
網友回覆:樓主，給貼子加分吧！！
冷簫輕笛好人！
網友回覆:非常感激 冷簫輕笛 .非常非常
我認為我應該給您加500分,可惜我沒有這麼多分,資料筆數已經正確.但其有三筆數量不正確.我正在查證.
分數我將稍後奉上.真心感謝
網友回覆:
SQL code
Code highlighting produced by Actipro CodeHighlighter (freeware)
--數量不正確的 可以查一下是否是更新語句的問題
--因為更新語句同樣需要合計的,之前沒有來得及寫
--有對應的話就更新
update a
set a.gr = a.gr b.planshu * b.f_planshu
from mrpplanpsn a inner join @MRPplan b
on a.psn = b.psn and a.materysn = b.materysn
---=======
--這樣改：
update a
set a.gr = a.gr sum(b.planshu * b.f_planshu)
from mrpplanpsn a inner join @MRPplan b
on a.psn = b.psn and a.materysn = b.materysn
group by a.psn,a.materysn
--其實合計在插入臨時表的時候合計也可以，但是忘記當時為什麼不在那個地方做了，思考ing...
網友回覆:冷簫輕笛您好 我將Excl 資料給您 您可以匯入您的SQL 資料庫嗎?
計算出來的資料不對,懇請您再次幫助我,謝謝
網友回覆:冷簫輕笛您好 我已發郵件給您 ,請查收
網友回覆:回覆:
--這樣改：
update a
set a.gr = a.gr sum(b.planshu * b.f_planshu)
from mrpplanpsn a inner join @MRPplan b
on a.psn = b.psn and a.materysn = b.materysn
group by a.psn,a.materysn
這樣語法是錯誤的,現在 Update 疲敝於否沒有任何變化.也就是說沒有起到作用
網友回覆:已經找到問題所在
思考中
網友回覆:mrpplanpsn表沒有資料對吧？
網友回覆:是的 我給您的mrpplanpsn表沒有結果,我只給了這個表的部分要害欄位
但目的是將結果寫到該表中
現在程式能做到將結果寫入mrpplanpsn 中, 給您的execl表 Sql2008 就是由 mrpplanpsn 匯出來的
要害問題是 有幾筆資料資料不對 這是致命的地方
謝謝您
網友回覆:
SQL code
Code highlighting produced by Actipro CodeHighlighter (freeware)
--重新搞了一下
CREATE proc P_UpdateMrpplanpsn
@Psn varchar(100) --改成你自己的型別
as
begin
--建立臨時表
declare @MRPplan table
(
layer int,
Psn varchar(100),
materysn varchar(100),
planshu decimal(8,2),
f_planshu int,
cdate datetime
)
declare @lay int
set @lay = 1
--根據psn匯入一級資料
insert into @MRPplan(layer,psn,materysn,planshu,f_planshu,cdate)
select @lay,psn,materysn,planshu,1,consigndate
from MRPplan
where Psn = @Psn
while (@@rowcount > 0)
begin
select @lay = @lay 1
insert into @MRPplan(layer,psn,materysn,planshu,f_planshu,cdate)
select @lay,@Psn,a.materysn,a.useshu,b.planshu * b.f_planshu ,b.cdate
from bom a inner join @MRPplan b on a.fmaterysn = b.materysn
where b.layer = @lay - 1 --not exists (select 1 from @MRPplan where psn = @Psn and
materysn = a.materysn)
end
--有對應的話就更新
update a
set a.gr = a.gr b.planshu
from mrpplanpsn a inner join (select psn,materysn,sum(planshu * f_planshu) as planshu,cdate
from @MRPplan
group by psn,materysn,cdate) b
on a.psn = b.psn and a.materysn = b.materysn
--沒有對應的話就插入
--這個就看你具體的需求了,因為mrpplanpsn表還有其他的列，這裡並不知道取值方法
insert into mrpplanpsn(psn,materysn,gr)
select a.psn,a.materysn ,sum(a.planshu * a.f_planshu) as planshu
from @MRPplan a left join mrpplanpsn b
on a.psn = b.psn and a.materysn = b.materysn
where b.materysn is null
group by a.psn,a.materysn
end
GO
--跟樓主的結果還有差異
/*
01-358M0101
01-358M0101-03B
01-358M0101-04B
01-358M0101-05B
*/
--這四種我這裡根本查不出來，但是樓主的結果裡有
--29-Z
--這個的計算結果跟樓主不一樣
--樓主可以用這個語句來手動計算
declare @m varchar(100)
set @m = '29-Z'
select a.psn as psn1,a.materysn as materysn1,a.planshu as planshu1,
b.materysn as materysn2,b.useshu as planshu2,
c.materysn as materysn3,c.useshu as planshu3,
d.materysn as materysn4,d.useshu as planshu4,
e.materysn as materysn5,e.useshu as planshu5,
f.materysn as materysn6,f.useshu as planshu6,
g.materysn as materysn7,g.useshu as planshu7
from mrpplan a left join bom b on a.materysn = b.fmaterysn
left join bom c on b.materysn = c.fmaterysn
left join bom d on c.materysn = d.fmaterysn
left join bom e on d.materysn = e.fmaterysn
left join bom f on e.materysn = f.fmaterysn
left join bom g on f.materysn = g.fmaterysn
WHERE a.psn = '2008' and
a.materysn = @m OR b.materysn = @m OR c.materysn = @m OR d.materysn = @m OR
e.materysn = @m OR f.materysn = @m
order by materysn1,materysn2,materysn3,materysn4,materysn5,materysn6
--以上語句我這裡的結果是(用文字開啟比較好看一些):
/*
psn1 materysn1 planshu1 materysn2
planshu2 materysn3 planshu3
materysn4 planshu4 materysn5 planshu5
materysn6 planshu6 materysn7
planshu7
-------------------- ------------------------------ -------------------- ---------------------
--------- -------------------------- ------------------------------ -------------------------- ---
--------------------------- -------------------------- ------------------------------ ------------
-------------- ------------------------------ -------------------------- -------------------------
----- --------------------------
2008 356-D1 100 01-356M01A01-05A
1.00 01-356M01A01-04A 1.00 01-
356M01A01-03A 1.00 01-356M01A01 1.00
29-Z 33.00 NULL
NULL
2008 358-D1 100 01-250/6653M0101
1.00 29-Z 1.00 NULL
NULL NULL NULL
NULL NULL NULL
NULL
2008 362-D1 100 01-362/363M0101-05A
1.00 01-362/363M0101-04A 1.00 01-
362/363M0101-03A 1.00 01-362/363M0101 1.00
29-Z 31.00 NULL
NULL
（所影響的行數為 3 行）
*/
--計算結果6500
--這個儲存過程依然沒有涉及到更新資料那塊,樓主最好搞點資料測下
**********************************************************
網友回覆: declare @MRPplan table
(
layer int,
Psn varchar(100),
materysn varchar(100),
planshu decimal(8,2),
f_planshu int,
cdate datetime
)
--這裡忘改了，改成這樣
f_planshu decimal(8,2),
網友回覆:真的是痛苦.這麼長啊.精神可嘉!
網友回覆:真心感謝 冷簫輕笛 .
您的專業,精神,讓我遵崇.
謝謝您 冷簫輕笛
您寫的以下程式碼就完全可以正確的計算出來結果
--重新搞了一下
CREATE proc P_UpdateMrpplanpsn
@Psn varchar(100) --改成你自己的型別
as
begin
--建立臨時表
declare @MRPplan table
(
layer int,
Psn varchar(100),
materysn varchar(100),
planshu decimal(8,2),
f_planshu int,
cdate datetime
)
declare @lay int
set @lay = 1
--根據psn匯入一級資料
insert into @MRPplan(layer,psn,materysn,planshu,f_planshu,cdate)
select @lay,psn,materysn,planshu,1,consigndate
from MRPplan
where Psn = @Psn
while (@@rowcount > 0)
begin
select @lay = @lay 1
insert into @MRPplan(layer,psn,materysn,planshu,f_planshu,cdate)
select @lay,@Psn,a.materysn,a.useshu,b.planshu * b.f_planshu ,b.cdate
from bom a inner join @MRPplan b on a.fmaterysn = b.materysn
where b.layer = @lay - 1 --not exists (select 1 from @MRPplan where psn = @Psn and materysn =
a.materysn)
end
--有對應的話就更新
update a
set a.gr = a.gr b.planshu
from mrpplanpsn a inner join (select psn,materysn,sum(planshu * f_planshu) as planshu,cdate
from @MRPplan
group by psn,materysn,cdate) b
on a.psn = b.psn and a.materysn = b.materysn
--沒有對應的話就插入
--這個就看你具體的需求了,因為mrpplanpsn表還有其他的列，這裡並不知道取值方法
insert into mrpplanpsn(psn,materysn,gr)
select a.psn,a.materysn ,sum(a.planshu * a.f_planshu) as planshu
from @MRPplan a left join mrpplanpsn b
on a.psn = b.psn and a.materysn = b.materysn
where b.materysn is null
group by a.psn,a.materysn
end
GO
()[@more@]
請各位前輩幫忙,線上等
主要 資料表單有 MRPplan,MRPplantou (生產單資料表), mrpplanpsn(物料需求單資料表),bom(BOM 資料表) 四個，
其中關聯欄位為Psn(生產單號),materysn(物料編號)
從 MRPplan 中依據Psn查詢資料進行迴圈,
a:假如mrpplanpsn 中有對應Psn的materysn則將MRPplan.planshu數量加到 mrpplanpsn.gr上
b:假如mrpplanpsn 中沒有有對應Psn的materysn則將MRPplan中相關資料寫入 mrpplanpsn
c:進行對MRPplan.materysn 資料進行BOM分解，若有子項則對子項進行以上動作
1: Select psn.materysn,planshu from MRPplan where psn=xxx (xxx 為傳入引數),對應資料集為rsq1
If rsq1.RecordCount> 0 then
Begin
For I:=1 To rsq1.RecordCount Do
Begin
fstr:=rsq1.materysn
Select * From mrpplanpsn where materysn=fstr and psn=xxx (xxx 為傳入引數),對應資料集為rst1
If rst1.RecordCount> 0 Then 將對應 rst1.gr=rst1.gr rsq1.planshu
Else
Begin
rst1.Append //新寫入對應內容
rst1.materysn=rsq1.materysn
rst1.gr=rsq1.planshu //數量
rst1.consigndate=rsq1.consigndate //交獲日期
...
End;
進行BOM資料對應查詢，rsq2.SQL.Add('select materysn from bom where fmaterysn=fstr）
If Rsq2.RecordCount Then SGmaterysn(Fstr;rsq1.planshu) 進行迴圈
Rsq2.Next
End //For I:=1 To rsq1.RecordCount Do
End;
SGmaterysn 過程如下:
select materysn,useshu from bom where fmaterysn=Fstr and useshu> 0
n:=Rsq2.RecordCount
If n > 0 Then
Begin
For i:= 1 To n do
begin
Fstr=Rsq2.Materysn
planshu:=Rsq2.Materysn
select materysn,gr,fu,psn,consigndate,remark from mrpplanpsn where materysn=Rsq2.Materysn and
psn=xxx
If rst2.RecordCount > 0 Then 則將對應 rst2.gr=rst2.gr rsq2.planshu
Else
Begin
rst2.Append //新寫入對應內容
rst2.materysn=rsq2.materysn
rst2.gr=rsq2.planshu
rst2.consigndate=rsq2.consigndate //交獲日期
...
End;
進行BOM資料對應查詢，rsq2.SQL.Add('select materysn from bom where fmaterysn=fstr）
If Rsq2.RecordCount > 0 Then SGmaterysn(Fstr;planshu) 進行遞迴迴圈
SGmaterysn(Fstr;planshu);
rsq2.Next ;
end; //For i:= 1 To n do
end;//If n > 0 Then
網友回覆:看著真累
網友回覆:其實看看前面一小段就大概明白了,後面是虛擬碼,加強說明的.麻煩大哥看看,幫幫忙.謝謝
網友回覆:請高手支招啊.看看前面幾行就大概明白了
網友回覆:1.BOM表中有個欄位是 fmaterysn ? 記錄父級materysn？
2.MRPplan,MRPplantou 這兩個表什麼關係？
網友回覆:大體寫了一個流程
主要的思路是先迴圈出所有的materysn
再更新，而不是找一層，更新一層
MRPplantou 表沒有用上，我估計可能MRPplan,MRPplantou 兩個表要關聯起來使用吧？
這個你自己改改就行
SQL code
Code highlighting produced by Actipro CodeHighlighter (freeware)
create proc p_1
@Psn varchar(100) --改成你自己的型別
as
begin
--建立臨時表(跟你的MRPplan表結構一樣就行)
declare @MRPplan table
(
Psn varchar(100),
materysn varchar(100),
planshu int,
....
)
--根據psn匯入資料
insert into @MRPplan(...)
select *
from MRPplan
where Psn = '你的引數'
--迴圈匯入子項
while (@@rowcount > 0)
begin
insert into @MRPplan(...)
select a.*
from MRPplan a inner join bom b on a.materysn = b.materysn
inner join @MRPplan c on b.fmaterysn = c.materysn
where a.materysn not in (select distinct materysn from @MRPplan)
end
--有對應的話就更新
update a
set a.gr = a.gr b.planshu
from mrpplanpsn a inner join @MRPplan b
on a.psn = b.psn and a.materysn = b.materysn
--沒有對應的話就插入
insert into mrpplanpsn(....)
select ...
from @MRPplan a left join mrpplanpsn b
on a.psn = b.psn and a.materysn = b.materysn
where b.materysn is null
end
***********************************************
樓主假如看了上面的思路還是沒法下手的話，建議樓主把四個表的結構貼出來，
每個表再抓20條資料出來，然後告訴大家你想要的結果，以及其中的演算法
這樣的話馬上就會有人給你答案的
網友回覆:我先測試看看 真是感激不盡
網友回覆:仍然沒有解決 期待幫忙 謝謝
資料表結構如下:
MRPplantou 生產單表頭
psn varchar(20) NOT NULL 生產單號
rev smallint(2) NOT NULL 版本號
makeren varchar(20) NOT NULL 制單人
makedate datetime(8) NOT NULL 制單日期
sureren varchar(20) NOT NULL 稽核人
suredate datetime(8) NOT NULL 稽核日期
state int(4) NOT NULL 稽核狀態 (0:未稽核;1:已刪除;2:已稽核;3:已算毛需求;4:已算可用量;5:已算淨需求 6:淨需求已稽核 8:
已結單 )
psn 20080104 生產單號
MRPplan 生產單表身
line smallint(2) NOT NULL 項次
psn varchar(20) NOT NULL 生產單號
materysn varchar(30) NOT NULL 物料編號
planshu numeric NOT NULL 批次 生產數量
consigndate smalldatetime(4) NOT NULL 交貨日期
remarks varchar(50) NULL 備註
Psn 20080104 (生產單號)
materysn 38912 (產品編號)
planshu 100 (生產數量)
BOM 基本資訊
fmaterysn char (30) 貨號編號
materysn char (30) 物料編號
son char (30)
useshu real (4) 用量
unit varchar (10) 單位
fmaterysn materysn useshu unit
38912 38912-1 10 pcs
38912-1 38912-1-1 10 pcs
38912 38912-2 10 pcs
38912-2 38912-2-1 10 pcs
38912-1 38912-1-2 10 pcs
mrpplanpsn MRP需求計算檔
psn varchar(20) NOT NULL 生產單號
materysn char (30) 物料遍號
son char (1)
gr numeric(9) NOT NULL 毛需求 (初始植為MRPPlan的Planshu 批次數 )
fu numeric(9) NULL 父階沖銷
stocks numeric(9) NULL 在庫量
sastocks numeric(9) NULL 安全庫存量
sr numeric(9) NULL 在途量
al numeric(9) NULL 保留量
ky numeric(9) NULL 可用量
nr numeric(9) NULL 淨需求
consigndate datetime NOT NULL 需求日期
remark varchar (200) 備註
網友回覆:我參照寫了一些 但是執行很慢 等了很久很久不見結果 ,請幫我看看
use yw
go
if exists(select name from sysobjects where name ='bom_level_ext1' and type='p') --判定是否存在該過程,存
在則刪除
drop proc dbo.bom_level_ext1
go
create procedure dbo.bom_level_ext1 --建立過程
@tmppsn varchar(20) --傳入引數
--with encryption
as --建立變數
declare @min_no varchar(20)
declare @rows_count integer
select @min_no = ''
create table #t_psn(psn varchar(20), materysn varchar(50) null,planshu float,consigndate
datetime,flag char(1) )
create index idex1 on #t_psn(materysn)
insert into #t_psn (psn, materysn,planshu,consigndate,flag)
select psn,materysn,isnull(planshu,0),consigndate ,'N' from MRPplan where psn=@tmppsn --傳入PSN 合同
編號
select @rows_count = count(#t_psn.materysn) from #t_psn --查詢#t_psn 資料筆數
if (@rows_count > 0 )
begin
select @min_no =min(#t_psn.materysn) from #t_psn where #t_psn.flag = 'N'
while not(@min_no is null or @min_no ='')
begin
--插入臨時表格資料
insert into #t_psn(materysn,planshu,flag) --對BOM查詢 看是否有子項
select dbo.bom.materysn,isnull(dbo.bom.useshu,0),'N'
from dbo.bom
where dbo.bom.fmaterysn = @min_no
******************************************
end
update #t_psn set #t_psn.flag = 'Y' where #t_psn.materysn = @min_no and #t_psn.flag='N'
select @min_no =min( #t_psn.materysn) from #t_psn where flag = 'N'
end
-- drop table #t_pno
select * from #t_psn order by materysn
--select * from #t_materysn
go
--grant all on dbo.bom_level_ext to users --賦權
--go
網友回覆:--這裡插入臨時表的flag列的資料都是'N'
insert into #t_psn (psn, materysn,planshu,consigndate,flag)
select psn,materysn,isnull(planshu,0),consigndate ,'N'
from MRPplan
where psn=@tmppsn --傳入PSN 合同編號
--這裡判定有沒有資料沒有錯，不過可以改為判定@@rowcount
select @rows_count = count(#t_psn.materysn) from #t_psn --查詢#t_psn 資料筆數
if (@rows_count > 0 )
begin
select @min_no =min(#t_psn.materysn) from #t_psn where #t_psn.flag = 'N'
--這裡判定是也是記錄數吧？上面不是已經判定了嗎？為什麼要再一次判定？
--而且為什麼要加條件呢，上面你插入的資料，只要有資料，就是'N'了
--這個while是個死迴圈吧，假如條件滿足的話，在迴圈體中間並沒有重新對變數進行賦值，所以會一直執行下去 while not(@min_no is
null or @min_no ='')
begin
--插入臨時表格資料
insert into #t_psn(materysn,planshu,flag) --對BOM查詢 看是否有子項
select dbo.bom.materysn,isnull(dbo.bom.useshu,0),'N'
from dbo.bom
where dbo.bom.fmaterysn = @min_no
end
update #t_psn set #t_psn.flag = 'Y' where #t_psn.materysn = @min_no and #t_psn.flag='N'
select @min_no =min( #t_psn.materysn) from #t_psn where flag = 'N'
end
網友回覆:我的本來意思是第一次插入 狀態標誌 Flag='N',滿足條件後將Flag='Y'
迴圈判定 while 中的引數 @min_no 前面已經賦值
網友回覆:--這兩句似乎也有點問題，@min_no 是查詢出的某個materysn值吧，樓主為什麼要對每個materysn逐條處理呢？
這個不能放在一起進行處理？
沒看懂業務，呵呵，猜得
update #t_psn set #t_psn.flag = 'Y' where #t_psn.materysn = @min_no and #t_psn.flag='N'
select @min_no =min( #t_psn.materysn) from #t_psn where flag = 'N'
網友回覆:迴圈判定while中的引數@min_no前面已經賦值
對阿，就是因為前面已經賦值了
所以比如@min_no = '112233',那麼它不是null，也不為空（''）
然後就執行while裡的語句
執行完了以後一看，@min_no 值還是 '112233'，
然後就又執行一次。。。
這不就是死迴圈嘛?
網友回覆:下班了，明天來關注！ ：）
網友回覆:要已manterysn 作為條件去BOM 中查詢是否有子項 有的話繼續迴圈
網友回覆:也就是要將 MRPplan.materysn 的產品38912 進行分解,分解依據是產品的BOM表, 計算出每個物料的用量,將其結果寫入
mrpplanpsn 物料需求計算表格
網友回覆:要已manterysn 作為條件去BOM 中查詢是否有子項 有的話繼續迴圈
--那這樣的思路有問題嗎？當然可能關聯條件是有問題的，具體的要樓主自己分析
--迴圈匯入子項
while (@@rowcount > 0)
begin
insert into @MRPplan(...)
select a.*
from MRPplan a inner join bom b on a.materysn = b.materysn
inner join @MRPplan c on b.fmaterysn = c.materysn
where a.materysn not in (select distinct materysn from @MRPplan)
end
樓主說這個思路不行，也要說出什麼地方不行來吧 。。。。
網友回覆:也就是要將 MRPplan.materysn 的產品38912 進行分解,分解依據是產品的BOM表, 計算出每個物料的用量,將其結果寫入
mrpplanpsn 物料需求計算表格
************************************
過程是不是這樣的？？？
select * from bom where fmaterysn = '38912'
比如結果集中有兩種物料
3891201 3891202
那就判定mrpplanpsn 表中有沒有，有就更新，沒有就插入
然後再看看3891201 3891202在bom表中有沒有子物料，有的話再重複
網友回覆:是的,的確是這樣.一直迴圈到 BOM 中沒有相關資料為止
網友回覆:冷簫輕笛 有在嗎? 繼續請教問題.昨天沒有上班
網友回覆:我略懂DELPHI,乾脆你要做什麼，直接給出來，給出表結構就OK
網友回覆:資料表結構如下:
我要做的事情就是要將MRPplan 中的產品依據BOM進行分解,然後將其資料與 mrpplanpsn (物料需求表)進行比對,假如有相同條件資料就對數
量想加 否則就新增. 比對條件是 PSN,Materysn 兩個要害字,MRPplan跟BOMDE的關聯欄位為materysn,fmaterysn.
MRPplantou 生產單表頭
psn varchar(20) NOT NULL 生產單號
rev smallint(2) NOT NULL 版本號
makeren varchar(20) NOT NULL 制單人
makedate datetime(8) NOT NULL 制單日期
sureren varchar(20) NOT NULL 稽核人
suredate datetime(8) NOT NULL 稽核日期
state int(4) NOT NULL 稽核狀態 (0:未稽核;1:已刪除;2:已稽核;3:已算毛需求;4:已算可用量;5:已算淨需求 6:淨需求已稽核 8:
已結單 )
psn 20080104 生產單號
MRPplan 生產單表身
line smallint(2) NOT NULL 項次
psn varchar(20) NOT NULL 生產單號
materysn varchar(30) NOT NULL 物料編號
planshu numeric NOT NULL 批次 生產數量
consigndate smalldatetime(4) NOT NULL 交貨日期
remarks varchar(50) NULL 備註
Psn 20080104 (生產單號)
materysn 38912 (產品編號)
planshu 100 (生產數量)
BOM 基本資訊
fmaterysn char (30) 貨號編號
materysn char (30) 物料編號
son char (30)
useshu real (4) 用量
unit varchar (10) 單位
fmaterysn materysn useshu unit
38912 38912-1 10 pcs
38912-1 38912-1-1 10 pcs
38912 38912-2 10 pcs
38912-2 38912-2-1 10 pcs
38912-1 38912-1-2 10 pcs
mrpplanpsn MRP需求計算檔
psn varchar(20) NOT NULL 生產單號
materysn char (30) 物料遍號
son char (1)
gr numeric(9) NOT NULL 毛需求 (初始植為MRPPlan的Planshu 批次數 )
fu numeric(9) NULL 父階沖銷
stocks numeric(9) NULL 在庫量
sastocks numeric(9) NULL 安全庫存量
sr numeric(9) NULL 在途量
al numeric(9) NULL 保留量
**************************************
ky numeric(9) NULL 可用量
nr numeric(9) NULL 淨需求
consigndate datetime NOT NULL 需求日期
remark varchar (200) 備註
網友回覆:假如從上面看下來,能基本瞭解到我的意圖.
假如仁兄肯幫忙,欲看Delphi 的原始碼,我可以發給你,
謝謝
網友回覆:產品依據BOM進行分解
===================
這是什麼意思？
網友回覆:來了
網友回覆:MRPplantou表 是不是沒有用阿？
因為你需要的流程是根據psn生產單號查詢planshu，然後更新mrpplanpsn表
對吧？
網友回覆:bom表中的三個欄位
fmaterysn char (30) 貨號編號
materysn char (30) 物料編號
son char (30)
哪兩個是用來標示父子關係的阿？
網友回覆:fmaterysn(父親) materysn(兒子) useshu(數量) unit(單位)
38912 38912-1 10 pcs
38912-1 38912-1-1 10 pcs
38912 38912-2 10 pcs
38912-2 38912-2-1 10 pcs
38912-1 38912-1-2 10 pcs
產品38912
1:將產品編號38912,PSN=xxx 至 之mrpplanpsn MRP需求計算檔查詢,沒有相關資料 則將將產品編號38912,PSN=xxx 寫入
mrpplanpsn中
2:對38912 至BOM 中查詢 是否有兒子,有 38912-1,38912-2 兩個,分別將其PSN=xxx至mrpplanpsn中比,假如有則進行數量想加否則
新增
3:對38912-1,38912-2至BOM中查詢....
網友回覆:MRPplantou表 該處可以不考慮,是執行完後將MRPplantou.state 狀態標誌改寫 為已經算料的狀態的意思
網友回覆:
SQL code
Code highlighting produced by Actipro CodeHighlighter (freeware)
--把上面的修改了一下，樓主看看還有什麼地方不妥
--有地方需要樓主自己改，比如找不到的話，就要插入到mrpplanpsn，但是mrpplanpsn表的列有好多阿。。。
--思路跟樓主的有點不一樣:我是先找產品編號38912，然後接著就找他的下級38912-1,38912-2，
--迴圈取到最低階，再進行資料處理。這樣的效率肯定會更好，但是不知道會不會符合樓主的需求
create proc P_UpdateMrpplanpsn
@Psn varchar(100) --改成你自己的型別
as
begin
--建立臨時表
declare @MRPplan table
(
Psn varchar(100),
materysn varchar(100),
planshu int
)
--根據psn匯入一級資料
insert into @MRPplan(psn,materysn,planshu)
select psn,materysn,planshu
from MRPplan
where Psn = @Psn
--迴圈匯入子項
while (@@rowcount > 0)
begin
insert into @MRPplan(psn,materysn,planshu)
select a.psn,a.materysn,a.planshu
from MRPplan a inner join bom b on a.materysn = b.materysn
inner join @MRPplan c on b.fmaterysn = c.materysn
where not exists (select 1 from @MRPplan where psn = a.psn and materysn = a.materysn)
end
--有對應的話就更新
update a
set a.gr = a.gr b.planshu
from mrpplanpsn a inner join @MRPplan b
on a.psn = b.psn and a.materysn = b.materysn
--沒有對應的話就插入
--這個就看你具體的需求了,因為mrpplanpsn表還有其他的列，這裡並不知道取值方法
insert into mrpplanpsn(psn,materysn,gr)
select a.psn,a.materysn ,a.planshu
from @MRPplan a left join mrpplanpsn b
on a.psn = b.psn and a.materysn = b.materysn
where b.materysn is null
end
網友回覆:先測試看看,真心感謝 冷簫輕笛.
網友回覆:@@rowcount 怎麼沒有賦值啊,他應該是誰的值
網友回覆:@@rowcount 是系統變數，記錄的是上一條sql語句執行後對資料庫影響的行數
網友回覆:MRPplan中 的materysn 可能只有2筆
但經過BOM分解後可能有50筆 ,所以 marplan.materysn=bom.materysn 應該是不對的
比如Mrpplan 只有38912 一筆
而由此產品 38912 分解出來的物料則有 38912,38912-1,38912-2,38912-1-1,38912-1-2,38912-2-1,38912-2-2
等等.
網友回覆:對阿，分解以後確實可能有很多筆
但是樓主別忘了，marplan.materysn=bom.materysn是寫在迴圈裡的
38912是在迴圈的上一步查到的，
迴圈中第一次查的是38912-1,38912-2
第二次查的是38912-1-1,38912-1-2,38912-2-1,38912-2-2
。。。。
為什麼會有問題呢？
網友回覆:結果是寫到@MRPplan 中吧,我最後用了一條 select * from @MRPplan 語句
我測試了,結果不對 但是不知原因
***********************************
===========================
他的子項38912-1,38912-2,38912-1-1,38912-1-2,38912-2-1,38912-2-2
在Mrpplan表裡沒有？？？？？？
網友回覆:是的,Mrpplan中只有38912一筆,但該產品進行分解在BOM中是有多筆的
網友回覆:少什麼記錄？
樓主可以這樣除錯：
注重：一定要一步一步的執行，每執行一步就檢視資料是否正確
這樣可以查詢出問題出在什麼地方了
SQL code
Code highlighting produced by Actipro CodeHighlighter (freeware)
--1.表變數改成臨時表
create table #MRPplan
(
Psn varchar(100),
materysn varchar(100),
planshu int,
)
--2.根據psn匯入資料
insert into #MRPplan(psn,materysn,planshu)
select psn,materysn,planshu
from MRPplan
where Psn = '你的引數'
--3.查詢資料,看看資料是否正確，假如正確就執行第4步
select * from #MRPplan
--4.匯入子項
insert into #MRPplan(psn,materysn,planshu)
select a.psn,a.materysn,a.planshu
from MRPplan a inner join bom b on a.materysn = b.materysn
inner join #MRPplan c on b.fmaterysn = c.materysn
where not exists (select 1 from #MRPplan where psn = a.psn and materysn = a.materysn)
--5.執行第3步
網友回覆:是的,Mrpplan中只有38912一筆,但該產品進行分解在BOM中是有多筆的
----------------------------
哦，這樣啊
那你已開始說的是：
a:假如mrpplanpsn 中有對應Psn的materysn則將MRPplan.planshu數量加到 mrpplanpsn.gr上
b:假如mrpplanpsn 中沒有有對應Psn的materysn則將MRPplan中相關資料寫入 mrpplanpsn
c:進行對MRPplan.materysn 資料進行BOM分解，若有子項則對子項進行以上動作
這樣的話c步驟中的子項的操作跟父項其實是不一樣的阿！
網友回覆:請問如何單步執行.謝謝
網友回覆:本來要用MRPplan.planshu欄位來更新mrpplanpsn.gr
但是子項沒有planshu，要用哪個欄位來更新阿？
網友回覆:--你是在查詢分析器裡測試吧？
選中你要執行的步驟語句，然後再點執行，不要直接點執行或者全選以後執行。
網友回覆:Bom 中的數量是useshu real (4) 用量
請問如何單步執行.謝謝
網友回覆:我指是一條一條語句執行
網友回覆:那子項中的psn是否也是取的開始傳入的@Psn變數呢？
假如是的話，把迴圈改成這樣：
SQL code
Code highlighting produced by Actipro CodeHighlighter (freeware)
--迴圈匯入子項
while (@@rowcount > 0)
begin
insert into @MRPplan(psn,materysn,planshu)
select @Psn,a.materysn,a.useshu
from bom a inner join @MRPplan b on a.fmaterysn = b.materysn
where not exists (select 1 from @MRPplan where psn = @Psn and materysn = a.materysn)
end
網友回覆:先把迴圈改了以後測試一下吧，不行的話再單步測試
網友回覆:
請問如何單步執行.謝謝
我指是一條一條語句執行
你指的是除錯程式那種單步執行？
這個sqlserver可不支援，呵呵，就得手動一條一條的執行
網友回覆:冷簫輕笛 您好:
1: 計算出來的結果是對的，但是有以下兩個問題需要解決。懇請繼續幫忙。不勝感激
2：結果表中的@MRPplan的materysn 為唯一值
也就是說假如有相同的要進行數量的累加.如下 mrpplanpsn.gr=mrpplanpsn.gr 分解該Bom的父親數量 * bom.useshu 即為
mrpplanpsn.gr=10(mrpplanpsn.gr 之前的) 傳入的materysn*bom.useshu
3：需要寫入日期 mrpplay.consigndate日期
網友回覆:1.不客氣
2.“分解該Bom的父親數量”
這個值是如何取出來的呢？
3.日期好辦，語句的最前面用
declare @date datetime
select @date = consigndate from mrpplan where psn = @psn
就可以了
然後表變數加上這列，插入資料的時候直接插入變數@date就可以了
網友回覆:哦，有點明白了，是不是這個意思？
比如：
38912-1,38912-2
子項38912-1-1,38912-1-2,38912-2-1,38912-2-2
假如38912-1的useshu = 10
那麼計算38912-1-1 的時候要用10 * useshu ？然後加在mrpplanpsn.gr上，而不是直接加useshu？
網友回覆:但是這句又不懂了。。。
即為 mrpplanpsn.gr=10(mrpplanpsn.gr 之前的) 傳入的materysn * bom.useshu
網友回覆:哦，consigndate 是mrpplan表裡的？那同一個psn裡不同的物料交貨時間不同？
假如這樣的話，那子項的consigndate如何來確定？
網友回覆:比如 A 是 A1,A2的父親 ,在BOM 中 為
fmaterysn materysn, useshu
A A 10
A A1 5
A A2 8
而且
mrpplanpsn 中已經有
psn materysn gr, consigndate
20080104 A1 20 2008/01/04
則下一個動作是進行gr累計
mrpplanpsn.gr=mrpplanpsn.gr 10*5
mrpplanpsn.gr=10 10*5
簡單的就是說 要做10個桌子,(1個桌子假如要4個腳) 則總腳數為 10 *4 =40
****************************************##########
然後再加其他產品需要這個腳的數目就是該定單或一些產品需要總腳的數目
總腳資料:=之前腳資料 10*40
網友回覆:
SQL code
Code highlighting produced by Actipro CodeHighlighter (freeware)
--再修改
create proc P_UpdateMrpplanpsn
@Psn varchar(100) --改成你自己的型別
as
begin
--建立臨時表
declare @MRPplan table
(
Psn varchar(100),
materysn varchar(100),
planshu int,
f_planshu int,
cdate datetime
)
--獲取consigndate
declare distinct @date = consigndate from mrpplay where psn = @psn
--根據psn匯入一級資料
insert into @MRPplan(psn,materysn,planshu,f_planshu,cdate)
select psn,materysn,planshu,1,@date
from MRPplan
where Psn = @Psn
--迴圈匯入子項
while (@@rowcount > 0)
begin
insert into @MRPplan(psn,materysn,planshu,f_planshu,cdate)
select @Psn,a.materysn,a.useshu,b.planshu,@date
from bom a inner join @MRPplan b on a.fmaterysn = b.materysn
where not exists (select 1 from @MRPplan where psn = @Psn and materysn = a.materysn)
end
--有對應的話就更新
update a
set a.gr = a.gr b.planshu * b.f_planshu
from mrpplanpsn a inner join @MRPplan b
on a.psn = b.psn and a.materysn = b.materysn
--沒有對應的話就插入
--這個就看你具體的需求了,因為mrpplanpsn表還有其他的列，這裡並不知道取值方法
insert into mrpplanpsn(psn,materysn,gr)
select a.psn,a.materysn ,a.planshu * a.f_planshu
from @MRPplan a left join mrpplanpsn b
on a.psn = b.psn and a.materysn = b.materysn
where b.materysn is null
end
網友回覆:fmaterysn materysn, useshu
A A 10
A A1 5
A A2 8
A1 A11 12
那麼計算A11的時候是否是 (5*10)*12 ?
假如是的話，上面的語句沒有問題，呵呵
網友回覆:哦，consigndate 是mrpplan表裡的？那同一個psn裡不同的物料交貨時間不同？
假如這樣的話，那子項的consigndate如何來確定？
網友回覆:過30了沒有？
網友回覆:consigndate 一個個固定的值 ,是mrpplan表裡的
網友回覆:哈哈,過了點點
網友回覆:上面的sql有問題，修改中
網友回覆:
SQL code
Code highlighting produced by Actipro CodeHighlighter (freeware)
--樓主，你給的資料庫俺用不了，公司的許可權管的嚴，呵呵，自己建了三個表，測試一下，樓主看看
--環境
create table mrpplan
(
psn varchar(20),
materysn varchar(30),
planshu numeric,
consigndate smalldatetime
)
insert into mrpplan(psn,materysn,planshu,consigndate)
select '20080104','38912',100,'2008-02-01'
create table bom
(
fmaterysn char (30), -- 貨號編號(父親)
materysn char (30), --物料編號(兒子)
useshu real , --用量
unit varchar (10) --單位
)
insert into bom(fmaterysn,materysn,useshu,unit) select '38912', '38912-1' , 10, 'pcs'
insert into bom(fmaterysn,materysn,useshu,unit) select '38912-1', '38912-1-1', 10, 'pcs'
insert into bom(fmaterysn,materysn,useshu,unit) select '38912', '38912-2' , 10, 'pcs'
insert into bom(fmaterysn,materysn,useshu,unit) select '38912-2', '38912-2-1', 10, 'pcs'
insert into bom(fmaterysn,materysn,useshu,unit) select '38912-1', '38912-1-2', 10, 'pcs'
CREATE TABLE [dbo].[MRPplanpsn] (
[psn] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[materysn] [char] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[son] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
[gr] [numeric](14, 4) NULL ,
[fu] [numeric](14, 4) NULL ,
[stocks] [numeric](14, 4) NULL ,
[sastocks] [numeric](14, 4) NULL ,
[sr] [numeric](14, 4) NULL ,
[al] [numeric](14, 4) NULL ,
[ky] [numeric](14, 4) NULL ,
[nr] [numeric](14, 4) NULL ,
[consigndate] [datetime] NULL ,
[remark] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
--建立儲存過程
create proc P_UpdateMrpplanpsn
@Psn varchar(100) --改成你自己的型別
as
begin
--建立臨時表
declare @MRPplan table
(
Psn varchar(100),
materysn varchar(100),
planshu int,
f_planshu int,
cdate datetime
)
--根據psn匯入一級資料
insert into @MRPplan(psn,materysn,planshu,f_planshu,cdate)
select psn,materysn,planshu,1,consigndate
from MRPplan
where Psn = @Psn
--迴圈匯入子項
while (@@rowcount > 0)
begin
insert into @MRPplan(psn,materysn,planshu,f_planshu,cdate)
select @Psn,a.materysn,a.useshu,b.planshu * b.f_planshu,b.cdate
from bom a inner join @MRPplan b on a.fmaterysn = b.materysn
where not exists (select 1 from @MRPplan where psn = @Psn and materysn = a.materysn)
end
--有對應的話就更新
update a
set a.gr = a.gr b.planshu * b.f_planshu
from mrpplanpsn a inner join @MRPplan b
on a.psn = b.psn and a.materysn = b.materysn
--沒有對應的話就插入
--這個就看你具體的需求了,因為mrpplanpsn表還有其他的列，這裡並不知道取值方法
insert into mrpplanpsn(psn,materysn,gr)
select a.psn,a.materysn ,a.planshu * a.f_planshu
from @MRPplan a left join mrpplanpsn b
on a.psn = b.psn and a.materysn = b.materysn
where b.materysn is null
end
--執行
exec P_UpdateMrpplanpsn '20080104'
--結果
psn materysn gr
-------------------- ------------------------------ ----------------
20080104 38912 100.0000
20080104 38912-1 1000.0000
20080104 38912-2 1000.0000
20080104 38912-1-1 10000.0000
20080104 38912-1-2 10000.0000
20080104 38912-2-1 10000.0000
（所影響的行數為 6 行）
--下班了，再有問題只好等下個周了，呵呵
**************************************************
網友回覆:冷簫輕笛 正解......
網友回覆:其中更新部分(如下)沒有執行任何動作
--有對應的話就更新
update a
set a.gr = a.gr b.planshu * b.f_planshu
from mrpplanpsn a inner join @MRPplan b
on a.psn = b.psn and a.materysn = b.materysn
以上程式碼疲敝與否結果是一樣的,也就是說 寫入 mrpplanpsn 中的資料與@MRPplan中的資料筆數是一樣的.
我測試了您以上資料,但是假如加入調整資料時就有問題
假如某產品(如產品 389122的產品結構對應的BOM)進行分解有對應 如下資料
insert into bom(fmaterysn,materysn,useshu,unit) select '38912-1', '38912-1-1', 20, 'pcs'
則計算出來資料是 7 筆 而非 6筆
--結果
psn materysn gr
-------------------- ------------------------------ ----------------
20080104 38912 100.0000
20080104 38912-1 1000.0000
20080104 38912-2 1000.0000
20080104 38912-1-1 10000.0000
20080104 38912-1-2 10000.0000
20080104 38912-2-1 10000.0000
20080104 38912-1-1 20000.0000
正確結果應為
--結果
psn materysn gr
-------------------- ------------------------------ ----------------
20080104 38912 100.0000
20080104 38912-1 1000.0000
20080104 38912-2 1000.0000
20080104 38912-1-1 30000.0000
20080104 38912-1-2 10000.0000
20080104 38912-2-1 10000.0000
望解
網友回覆:其中更新部分(如下)沒有執行任何動作
--有對應的話就更新
update a
set a.gr = a.gr b.planshu * b.f_planshu
from mrpplanpsn a inner join @MRPplan b
on a.psn = b.psn and a.materysn = b.materysn
以上程式碼疲敝與否結果是一樣的,也就是說 寫入 mrpplanpsn 中的資料與@MRPplan中的資料筆數是一樣的.
-----------------------------------------------
這個你是用我的資料測試的？
那肯定是有沒有都一樣啊，我的測試環境本來裡面就滅有資料，當然只做插入不做更新阿
假如不是，請說明什麼情況下的什麼資料不能更新
==================================================================
你的bom表答應這樣的紀錄同時存在？？？？
'38912-1', '38912-1-1', 10, 'pcs'
'38912-1', '38912-1-1', 20, 'pcs'
假如是的話，那跟我理解的bom表是不一樣的。
SQL code
Code highlighting produced by Actipro CodeHighlighter (freeware)
--迴圈匯入子項
while (@@rowcount > 0)
begin
insert into @MRPplan(psn,materysn,planshu,f_planshu,cdate)
select @Psn,a.materysn,a.useshu,b.planshu * b.f_planshu,b.cdate
from bom a inner join @MRPplan b on a.fmaterysn = b.materysn
where not exists (select 1 from @MRPplan where psn = @Psn and materysn = a.materysn)
end
--改成這個試試：
--迴圈匯入子項
while (@@rowcount > 0)
begin
insert into @MRPplan(psn,materysn,planshu,f_planshu,cdate)
select @Psn,a.materysn,sum(a.useshu),sum(b.planshu * b.f_planshu) ,b.cdate
from bom a inner join @MRPplan b on a.fmaterysn = b.materysn
where not exists (select 1 from @MRPplan where psn = @Psn and materysn = a.materysn)
group by a.materysn,b.cdate
end
****************************************************************
網友回覆:--好累，蓋了六十多樓了，問題還沒有敘述明白。。。。 -_-!!
網友回覆:樓主，給貼子加分吧！！
冷簫輕笛好人！
網友回覆:非常感激 冷簫輕笛 .非常非常
我認為我應該給您加500分,可惜我沒有這麼多分,資料筆數已經正確.但其有三筆數量不正確.我正在查證.
分數我將稍後奉上.真心感謝
網友回覆:
SQL code
Code highlighting produced by Actipro CodeHighlighter (freeware)
--數量不正確的 可以查一下是否是更新語句的問題
--因為更新語句同樣需要合計的,之前沒有來得及寫
--有對應的話就更新
update a
set a.gr = a.gr b.planshu * b.f_planshu
from mrpplanpsn a inner join @MRPplan b
on a.psn = b.psn and a.materysn = b.materysn
---=======
--這樣改：
update a
set a.gr = a.gr sum(b.planshu * b.f_planshu)
from mrpplanpsn a inner join @MRPplan b
on a.psn = b.psn and a.materysn = b.materysn
group by a.psn,a.materysn
--其實合計在插入臨時表的時候合計也可以，但是忘記當時為什麼不在那個地方做了，思考ing...
網友回覆:冷簫輕笛您好 我將Excl 資料給您 您可以匯入您的SQL 資料庫嗎?
計算出來的資料不對,懇請您再次幫助我,謝謝
網友回覆:冷簫輕笛您好 我已發郵件給您 ,請查收
網友回覆:回覆:
--這樣改：
update a
set a.gr = a.gr sum(b.planshu * b.f_planshu)
from mrpplanpsn a inner join @MRPplan b
on a.psn = b.psn and a.materysn = b.materysn
group by a.psn,a.materysn
這樣語法是錯誤的,現在 Update 疲敝於否沒有任何變化.也就是說沒有起到作用
網友回覆:已經找到問題所在
思考中
網友回覆:mrpplanpsn表沒有資料對吧？
網友回覆:是的 我給您的mrpplanpsn表沒有結果,我只給了這個表的部分要害欄位
但目的是將結果寫到該表中
現在程式能做到將結果寫入mrpplanpsn 中, 給您的execl表 Sql2008 就是由 mrpplanpsn 匯出來的
要害問題是 有幾筆資料資料不對 這是致命的地方
謝謝您
網友回覆:
SQL code
Code highlighting produced by Actipro CodeHighlighter (freeware)
--重新搞了一下
CREATE proc P_UpdateMrpplanpsn
@Psn varchar(100) --改成你自己的型別
as
begin
--建立臨時表
declare @MRPplan table
(
layer int,
Psn varchar(100),
materysn varchar(100),
planshu decimal(8,2),
f_planshu int,
cdate datetime
)
declare @lay int
set @lay = 1
--根據psn匯入一級資料
insert into @MRPplan(layer,psn,materysn,planshu,f_planshu,cdate)
select @lay,psn,materysn,planshu,1,consigndate
from MRPplan
where Psn = @Psn
while (@@rowcount > 0)
begin
select @lay = @lay 1
insert into @MRPplan(layer,psn,materysn,planshu,f_planshu,cdate)
select @lay,@Psn,a.materysn,a.useshu,b.planshu * b.f_planshu ,b.cdate
from bom a inner join @MRPplan b on a.fmaterysn = b.materysn
where b.layer = @lay - 1 --not exists (select 1 from @MRPplan where psn = @Psn and
materysn = a.materysn)
end
--有對應的話就更新
update a
set a.gr = a.gr b.planshu
from mrpplanpsn a inner join (select psn,materysn,sum(planshu * f_planshu) as planshu,cdate
from @MRPplan
group by psn,materysn,cdate) b
on a.psn = b.psn and a.materysn = b.materysn
--沒有對應的話就插入
--這個就看你具體的需求了,因為mrpplanpsn表還有其他的列，這裡並不知道取值方法
insert into mrpplanpsn(psn,materysn,gr)
select a.psn,a.materysn ,sum(a.planshu * a.f_planshu) as planshu
from @MRPplan a left join mrpplanpsn b
on a.psn = b.psn and a.materysn = b.materysn
where b.materysn is null
group by a.psn,a.materysn
end
GO
--跟樓主的結果還有差異
/*
01-358M0101
01-358M0101-03B
01-358M0101-04B
01-358M0101-05B
*/
--這四種我這裡根本查不出來，但是樓主的結果裡有
--29-Z
--這個的計算結果跟樓主不一樣
--樓主可以用這個語句來手動計算
declare @m varchar(100)
set @m = '29-Z'
select a.psn as psn1,a.materysn as materysn1,a.planshu as planshu1,
b.materysn as materysn2,b.useshu as planshu2,
c.materysn as materysn3,c.useshu as planshu3,
d.materysn as materysn4,d.useshu as planshu4,
e.materysn as materysn5,e.useshu as planshu5,
f.materysn as materysn6,f.useshu as planshu6,
g.materysn as materysn7,g.useshu as planshu7
from mrpplan a left join bom b on a.materysn = b.fmaterysn
left join bom c on b.materysn = c.fmaterysn
left join bom d on c.materysn = d.fmaterysn
left join bom e on d.materysn = e.fmaterysn
left join bom f on e.materysn = f.fmaterysn
left join bom g on f.materysn = g.fmaterysn
WHERE a.psn = '2008' and
a.materysn = @m OR b.materysn = @m OR c.materysn = @m OR d.materysn = @m OR
e.materysn = @m OR f.materysn = @m
order by materysn1,materysn2,materysn3,materysn4,materysn5,materysn6
--以上語句我這裡的結果是(用文字開啟比較好看一些):
/*
psn1 materysn1 planshu1 materysn2
planshu2 materysn3 planshu3
materysn4 planshu4 materysn5 planshu5
materysn6 planshu6 materysn7
planshu7
-------------------- ------------------------------ -------------------- ---------------------
--------- -------------------------- ------------------------------ -------------------------- ---
--------------------------- -------------------------- ------------------------------ ------------
-------------- ------------------------------ -------------------------- -------------------------
----- --------------------------
2008 356-D1 100 01-356M01A01-05A
1.00 01-356M01A01-04A 1.00 01-
356M01A01-03A 1.00 01-356M01A01 1.00
29-Z 33.00 NULL
NULL
2008 358-D1 100 01-250/6653M0101
1.00 29-Z 1.00 NULL
NULL NULL NULL
NULL NULL NULL
NULL
2008 362-D1 100 01-362/363M0101-05A
1.00 01-362/363M0101-04A 1.00 01-
362/363M0101-03A 1.00 01-362/363M0101 1.00
29-Z 31.00 NULL
NULL
（所影響的行數為 3 行）
*/
--計算結果6500
--這個儲存過程依然沒有涉及到更新資料那塊,樓主最好搞點資料測下
**********************************************************
網友回覆: declare @MRPplan table
(
layer int,
Psn varchar(100),
materysn varchar(100),
planshu decimal(8,2),
f_planshu int,
cdate datetime
)
--這裡忘改了，改成這樣
f_planshu decimal(8,2),
網友回覆:真的是痛苦.這麼長啊.精神可嘉!
網友回覆:真心感謝 冷簫輕笛 .
您的專業,精神,讓我遵崇.
謝謝您 冷簫輕笛
您寫的以下程式碼就完全可以正確的計算出來結果
--重新搞了一下
CREATE proc P_UpdateMrpplanpsn
@Psn varchar(100) --改成你自己的型別
as
begin
--建立臨時表
declare @MRPplan table
(
layer int,
Psn varchar(100),
materysn varchar(100),
planshu decimal(8,2),
f_planshu int,
cdate datetime
)
declare @lay int
set @lay = 1
--根據psn匯入一級資料
insert into @MRPplan(layer,psn,materysn,planshu,f_planshu,cdate)
select @lay,psn,materysn,planshu,1,consigndate
from MRPplan
where Psn = @Psn
while (@@rowcount > 0)
begin
select @lay = @lay 1
insert into @MRPplan(layer,psn,materysn,planshu,f_planshu,cdate)
select @lay,@Psn,a.materysn,a.useshu,b.planshu * b.f_planshu ,b.cdate
from bom a inner join @MRPplan b on a.fmaterysn = b.materysn
where b.layer = @lay - 1 --not exists (select 1 from @MRPplan where psn = @Psn and materysn =
a.materysn)
end
--有對應的話就更新
update a
set a.gr = a.gr b.planshu
from mrpplanpsn a inner join (select psn,materysn,sum(planshu * f_planshu) as planshu,cdate
from @MRPplan
group by psn,materysn,cdate) b
on a.psn = b.psn and a.materysn = b.materysn
--沒有對應的話就插入
--這個就看你具體的需求了,因為mrpplanpsn表還有其他的列，這裡並不知道取值方法
insert into mrpplanpsn(psn,materysn,gr)
select a.psn,a.materysn ,sum(a.planshu * a.f_planshu) as planshu
from @MRPplan a left join mrpplanpsn b
on a.psn = b.psn and a.materysn = b.materysn
where b.materysn is null
group by a.psn,a.materysn
end
GO
()[@more@]
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/93029/viewspace-1023016/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- SQL 2000 2005通用儲存過程SQL儲存過程
- SQL Server 2000 的分頁查詢(儲存過程)SQLServer儲存過程
- 編寫和優化SQL Server的儲存過程優化SQLServer儲存過程
- SQL儲存過程示例SQL儲存過程
- SQL Server 儲存過程SQLServer儲存過程
- 【SQL Server】--儲存過程SQLServer儲存過程
- SQL 儲存過程裡呼叫另一個儲存過程SQL儲存過程
- 小寫轉大寫金額儲存過程[SQL SERVER]儲存過程SQLServer
- Excel資料匯入SQL Server2000的儲存過程ExcelSQLServer儲存過程
- 用XML和SQL 2000來管理儲存過程呼叫(轉)XMLSQL儲存過程
- 編寫JAVA儲存過程Java儲存過程
- SQL 分頁儲存過程SQL儲存過程
- MS SQL Server儲存過程SQLServer儲存過程
- Sql Server系列：儲存過程SQLServer儲存過程
- SQL 建立儲存過程PROCEDURESQL儲存過程
- 解密SQL SERVER儲存過程解密SQLServer儲存過程
- SQL儲存過程迴圈SQL儲存過程
- sql儲存過程分頁SQL儲存過程
- 生成sql server2000物件建立指令碼的儲存過程(轉)SQLServer物件指令碼儲存過程
- SQL Server 儲存過程的運用SQLServer儲存過程
- (SQL Server)分頁的儲存過程SQLServer儲存過程
- SQL分隔字串的儲存過程 (轉)SQL字串儲存過程
- 兩種SQL分頁方法儲存過程和遊標儲存過程SQL儲存過程
- Sql儲存過程分頁--臨時表儲存SQL儲存過程
- oracle儲存過程書寫格式Oracle儲存過程
- sql 儲存過程命名規範SQL儲存過程
- Oracle Pl/SQL 之 儲存過程OracleSQL儲存過程
- PL/SQL 05 儲存過程 procedureSQL儲存過程
- SQL Server基礎：儲存過程SQLServer儲存過程
- sql server儲存過程語法SQLServer儲存過程
- SQL總結（五）儲存過程SQL儲存過程
- MySQL的寫入資料儲存過程MySql儲存過程
- SQL Server儲存過程中raiserror的使用SQLServer儲存過程AIError
- 在儲存過程中寫truncate table儲存過程
- Oracle SQL Developer Debug儲存過程OracleSQLDeveloper儲存過程
- SQL Server基礎之儲存過程SQLServer儲存過程
- oracle動態sql儲存過程示例OracleSQL儲存過程
- 從sybase的儲存過程轉向oracle的儲存過程儲存過程Oracle