用VB+ACCESS做的,SQL的话思路应该差不多
Dim Conn As New ADODB.Connection, Rs As New ADODB.Recordset, Rs_1 As New ADODB.Recordset
Dim PN As String, Component As String, Component_Type As String, Usage As Integer
Private Sub BOM_Run()
PN = A
Rs.Open "Select * from BOM where Assembly = '" & PN & "'", Conn, 1, 1
------ BOM为上阶材料和下阶材料的对应关系表
If Not Rs.EOF Then
Rs.Movefirst
Do While Not Rs.EOF
Component = Rs.Fileds("Component")
Component_Type = Rs.Fileds("Component_Type")
Usage = Rs.Fileds("Usage")
Conn.Excute ("Insert into BOM_A Values('" & PN & "','" & Component & "','" & Component_Type & "'," & Usage & ")")
--- BOM_A 为 A料的BOM表
Rs.MoveNext
Loop
Rs.Close
Else
MsgBox "没有找到材料BOM:" & PN
Rs.Close
Exit Sub
End If
Rs_Qty = 1
Do Until Rs_Qty = 0
Rs.Open "Select * from BOM_A where [Component Type] <> '" & RawPart & "'", Conn, 1, 1
Rs_Qty = Rs.RecordCount
If Rs_Qty <> 0 Then
Rs.Movefirst
Do While Not Rs.EOF
PN = Rs.Fileds("Component")
Rs_1.Open "Select * from BOM where Assembly = '" & PN & "'", Conn, 1, 1
If Not Rs_1.EOF Then
Rs_1.Movefirst
Do While Not Rs.EOF
Component = Rs_1.Fileds("Component")
Component_Type = Rs_1.Fileds("Component_Type")
Usage = Rs_1.Fileds("Usage")
Conn.Excute ("Insert into BOM_A Values('" & PN & "','" & Component & "','" & Component_Type & "'," & Usage & ")")
Rs_1.MoveNext
Loop
Rs.Close
Else
MsgBox "没有找到材料BOM:" & PN
Rs_1.Close
Exit Sub
End If
Rs.MoveNext
Loop
Rs.Close
Else
Rs.Close
Exit Do
End If
Loop
MsgBox "展BOM成功!"
End Sub