SQL方法:
Dim sql,sql1,s1,s2,s3 As String
Dim wn1 As Integer = DatePart("ww" , Date.Today)
Dim cpts As List(Of String) = DataTables("生产计划").GetValues("产品","产品 Is Not Null")
Dim ljts As List(Of String) = DataTables("零件用量").GetValues("零件","零件 Is Not Null")
s1 =" As 数量 From{生产计划} Where 产品 Is Not Null Union All Select 产品, WK"
s2 = "Select 产品,sum(数量) As 数量 From(Select 产品, WK" & wn1 & s1 & wn1+1 & s1 & wn1+2
s2+= " As 数量 From{生产计划} Where 产品 Is Not Null) Group By 产品"
s3 = " Union All Select 产品,零件,用量 From{零件用量} Where 产品 ='"
For Each ljt As String In ljts
For Each cpt As String In cpts
sql+= s3 & cpt & "' And 零件 ='" & ljt & "'"
Next
Next
s1="Select 零件,Sum(需求量) As 需求量,Sum(需求量) As 需补量 From("
s1+="Select a.产品,零件,用量*数量 As 需求量,'' As 需补量 FROM("
sql =s1 & s2 & ") a INNER JOIN (" & sql.substring(11) & ") b ON a.产品 = b.产品) Group By 零件"
sql ="Select a.零件,需求量, (数量-需补量) As 需补量 FROM(" & sql & ") a INNER JOIN {零件库存} b ON a.零件 = b.零件"
Tables(e.Form.Name & "_Table1" ).Fill(sql,"",True)
加好友 
发短信
Post By：2014/3/13 17:58:00 [只看该作者]
