各位VB高手,谁能帮我把这段SQL语句用VB6来描述!谢了!
这是2个存储过程:create procedure GetBomTrueList
@AssBomName varchar(20),@pointName varchar(20),@expre varchar(500)
as
begin
declare @sl decimal(20,4)
declare @expression varchar(500)
declare @point varchar(20) declare @bomTop varchar(20)
declare #point_cursor cursor LOCAL for
select distinct point,sl from bom where Assbom = @pointName
open #point_cursor fetch next from #point_cursor
into @point,@sl while @@FETCH_STATUS = 0 --遊標執行的狀態
begin
--如果沒有下級節點了,就加入到數據表中
if(exists(select point from bom where Assbom = @point))
begin
--如果有下級節點,則再次循环
select @expression = rtrim(@expre) + '*' + rtrim(convert(char(25),@sl))
exec('GetBomTrueList ' + @AssBomName + ',' + @point + ',"' + @expression + '"')
end
else
begin
insert into #bomTemp values (@AssBomName,@point,@sl,@expre + '*' + ltrim(rtrim(convert(char(25),@sl))))
end
fetch next from #point_cursor
into @point,@sl
end close #point_cursor
deallocate #point_cursor
end
go
create procedure GetBomList
asbegin create table #bomTemp (Assbom varchar(20),point varchar(20),sl decimal(20,4),expression varchar(500))
create table #bomLast (Assbom varchar(20),point varchar(20),sl decimal(20,4))
--調用遞歸
declare @bomTop varchar(20)
declare bom_cursor cursor for --得到頂級產品
select distinct Assbom from bom where Assbom not in (select point from bom) open bom_cursor fetch next from bom_cursor
into @bomTop while @@FETCH_STATUS = 0
begin
Exec('GetBomTrueList ' + @bomTop +','+@bomTop+','+'1') fetch next from bom_cursor
into @bomTop
end close bom_cursor
deallocate bom_cursor --獲得到數據及運算表達式後,再次利用數據表中的表達式運算一下
declare @AssbomT varchar(20)
declare @point varchar(20)
declare @expression varchar(500)
declare @value decimal(20,4) declare @execUpdate varchar(500)
declare bom_cursor_end cursor for
select Assbom,point,expression from #bomTemp
open bom_cursor_end
fetch next from bom_cursor_end
into @AssbomT,@point,@expression
while @@FETCH_STATUS = 0
begin
set @execUpdate = 'Insert into #bomLast values(' + char(39) + rtrim(@AssbomT) + char(39) + ',' + char(39) + rtrim(@point) + char(39) + ',' + rtrim(@expression) + ')'
--set @execUpdate = 'update #bomTemp set sl = ' + rtrim(@expression) + ' where Assbom = ' + char(39) + rtrim(@AssbomT) + char(39) + ' and point = ' + char(39) + rtrim(@point) + char(39)
exec(@execUpdate)
fetch next from bom_cursor_end
into @AssbomT,@point,@expression
end close bom_cursor_end
deallocate bom_cursor_end select Assbom,point,sum(sl) as sl from #bomLast group by Assbom,point drop table #bomTemp
drop table #bomLast
end
这是2个存储过程:create procedure GetBomTrueList
@AssBomName varchar(20),@pointName varchar(20),@expre varchar(500)
as
begin
declare @sl decimal(20,4)
declare @expression varchar(500)
declare @point varchar(20) declare @bomTop varchar(20)
declare #point_cursor cursor LOCAL for
select distinct point,sl from bom where Assbom = @pointName
open #point_cursor fetch next from #point_cursor
into @point,@sl while @@FETCH_STATUS = 0 --遊標執行的狀態
begin
--如果沒有下級節點了,就加入到數據表中
if(exists(select point from bom where Assbom = @point))
begin
--如果有下級節點,則再次循环
select @expression = rtrim(@expre) + '*' + rtrim(convert(char(25),@sl))
exec('GetBomTrueList ' + @AssBomName + ',' + @point + ',"' + @expression + '"')
end
else
begin
insert into #bomTemp values (@AssBomName,@point,@sl,@expre + '*' + ltrim(rtrim(convert(char(25),@sl))))
end
fetch next from #point_cursor
into @point,@sl
end close #point_cursor
deallocate #point_cursor
end
go
create procedure GetBomList
asbegin create table #bomTemp (Assbom varchar(20),point varchar(20),sl decimal(20,4),expression varchar(500))
create table #bomLast (Assbom varchar(20),point varchar(20),sl decimal(20,4))
--調用遞歸
declare @bomTop varchar(20)
declare bom_cursor cursor for --得到頂級產品
select distinct Assbom from bom where Assbom not in (select point from bom) open bom_cursor fetch next from bom_cursor
into @bomTop while @@FETCH_STATUS = 0
begin
Exec('GetBomTrueList ' + @bomTop +','+@bomTop+','+'1') fetch next from bom_cursor
into @bomTop
end close bom_cursor
deallocate bom_cursor --獲得到數據及運算表達式後,再次利用數據表中的表達式運算一下
declare @AssbomT varchar(20)
declare @point varchar(20)
declare @expression varchar(500)
declare @value decimal(20,4) declare @execUpdate varchar(500)
declare bom_cursor_end cursor for
select Assbom,point,expression from #bomTemp
open bom_cursor_end
fetch next from bom_cursor_end
into @AssbomT,@point,@expression
while @@FETCH_STATUS = 0
begin
set @execUpdate = 'Insert into #bomLast values(' + char(39) + rtrim(@AssbomT) + char(39) + ',' + char(39) + rtrim(@point) + char(39) + ',' + rtrim(@expression) + ')'
--set @execUpdate = 'update #bomTemp set sl = ' + rtrim(@expression) + ' where Assbom = ' + char(39) + rtrim(@AssbomT) + char(39) + ' and point = ' + char(39) + rtrim(@point) + char(39)
exec(@execUpdate)
fetch next from bom_cursor_end
into @AssbomT,@point,@expression
end close bom_cursor_end
deallocate bom_cursor_end select Assbom,point,sum(sl) as sl from #bomLast group by Assbom,point drop table #bomTemp
drop table #bomLast
end
dim--所有的declare
游标对应的是dim rs as adodb.recordset
dim conn as adodb.connection并且要记得引用ado对象
并把conn连接到你的数据库
rs.open sql,conn,1,3
while not rs.eof 相当于你的游标循环
设置rs1
if(exists(select point from bom where Assbom = @point))
相当于
rs1.open 上面个个sql,conn,1,3
if not rs1.eof then
取得值的sql略有不同
执行这部其实是一样的
else
还要建立一个rs2
rs2.open 这个insert语句end ifwend
end function 这个存储过程完了
@AssBomName varchar(20),@pointName varchar(20),@expre varchar(500)
as
begin
dim @sl decimal(20,4)
dim @expression varchar(500)
dim @point varchar(20) dim @bomTop varchar(20)
dim #point_cursor cursor LOCAL for
select distinct point,sl from bom where Assbom = @pointName
open #point_cursor fetch next from #point_cursor
into @point,@sl while @@FETCH_STATUS = 0 --遊標執行的狀態
begin
--如果沒有下級節點了,就加入到數據表中
if(exists(select point from bom where Assbom = @point))
begin
--如果有下級節點,則再次循环
select @expression = rtrim(@expre) + '*' + rtrim(convert(char(25),@sl))
exec('GetBomTrueList ' + @AssBomName + ',' + @point + ',"' + @expression + '"')
end
else
begin
insert into #bomTemp values (@AssBomName,@point,@sl,@expre + '*' + ltrim(rtrim(convert(char(25),@sl))))
end
fetch next from #point_cursor
into @point,@sl
end close #point_cursor
deallocate #point_cursor
end
go
create procedure GetBomList
asbegin create table #bomTemp (Assbom varchar(20),point varchar(20),sl decimal(20,4),expression varchar(500))
create table #bomLast (Assbom varchar(20),point varchar(20),sl decimal(20,4))
--調用遞歸
dim @bomTop varchar(20)
dim bom_cursor cursor for --得到頂級產品
select distinct Assbom from bom where Assbom not in (select point from bom) open bom_cursor fetch next from bom_cursor
into @bomTop while @@FETCH_STATUS = 0
begin
Exec('GetBomTrueList ' + @bomTop +','+@bomTop+','+'1') fetch next from bom_cursor
into @bomTop
end close bom_cursor
deallocate bom_cursor --獲得到數據及運算表達式後,再次利用數據表中的表達式運算一下
dim @AssbomT varchar(20)
dim @point varchar(20)
dim @expression varchar(500)
dim @value decimal(20,4) dim @execUpdate varchar(500)
dim bom_cursor_end cursor for
select Assbom,point,expression from #bomTemp
open bom_cursor_end
fetch next from bom_cursor_end
into @AssbomT,@point,@expression
while @@FETCH_STATUS = 0
begin
set @execUpdate = 'Insert into #bomLast values(' + char(39) + rtrim(@AssbomT) + char(39) + ',' + char(39) + rtrim(@point) + char(39) + ',' + rtrim(@expression) + ')'
--set @execUpdate = 'update #bomTemp set sl = ' + rtrim(@expression) + ' where Assbom = ' + char(39) + rtrim(@AssbomT) + char(39) + ' and point = ' + char(39) + rtrim(@point) + char(39)
exec(@execUpdate)
fetch next from bom_cursor_end
into @AssbomT,@point,@expression
end close bom_cursor_end
deallocate bom_cursor_end select Assbom,point,sum(sl) as sl from #bomLast group by Assbom,point drop table #bomTemp
drop table #bomLast
end
FG SA1 2
FG SA2 3
SA1 PT1 4
SA1 PT2 5
SA2 PT1 6
SA2 PT3 7
要得出这样的结果(相当于做一个成品需要多少零件PT): FG PT1 26
FG PT2 10
FG PT3 21
dim point as string
dim bomTop as string
sql="select distinct point,sl from bom where Assbom = " & pointName ‘这个变量你从哪弄的??
dim rs as adodb.recordset dim conn as adodb.connection
rs.open sql,conn,1,3
while(not rs.eof)
set rs1=createobject("adodb.recordset")
sql1="select point from bom where Assbom =" & rs("point")
rs1.open sql,conn,1,3
if not rs1.eof then
select @expression = rtrim(@expre) + '*' + rtrim(convert(char(25),@sl))你这个式子是什么东西的??
end if
set rs1=nothing
wend
expression是数据表中的一个字段,我我忘了打上去了
从数据库中获取呀
你再看一下看,行不行
FG SA1 2
FG SA2 3
SA1 PT1 4
SA1 PT2 5
SA2 PT1 6
SA2 PT3 7
要得出这样的结果(相当于做一个成品需要多少零件PT): FG PT1 26
FG PT2 10
FG PT3 21
dim expression as string '这么个改法,我晕了,你是一点儿不会vb的说??
dim point as string
dim bomTop as string
sql="select distinct point,sl from bom where Assbom = " & pointName ‘这个变量你从哪弄的??从外边输入的话,我写的就是对的
dim rs as adodb.recordset dim conn as adodb.connection
rs.open sql,conn,1,3
while(not rs.eof)
set rs1=createobject("adodb.recordset")
sql1="select point from bom where Assbom =" & rs("point")
rs1.open sql,conn,1,3
if not rs1.eof then
select @expression = rtrim(@expre) + '*' + rtrim(convert(char(25),@sl))关键是不知道其中的逻辑关系
else
sql2="insert into bomTemp values (" & AssBomName & "," & point & "," & sl & "," &
@expre + '*' + ltrim(rtrim(convert(char(25),@sl))))------>最后这个expre变量不清楚是什么东东还有这个convert后是什么东东
end if
set rs1=nothing
wend end function