1、VB 程序使用存储过程,参数已经传递,为何不显示结果,而同样语句在SQL 查询中显示正常str = "EXECUTE pr_jxc_itemcls_amt '" & as_from & "' ,'" & as_to & "','" & as_branchno & "%','" & as_supno & "%','" & as_itemcls & "%'"
rs.Open str, connrs, 3, 2
lastrow = 1
lastcol = 1
With grid_master
.Editable = flexEDNone
.AllowUserResizing = flexResizeBoth
.AllowUserFreezing = flexFreezeBoth
.BackColorFrozen = RGB(200, 200, 255)
.ExplorerBar = flexExMoveRows Or flexExSortShowAndMove
Set .DataSource = rs
.SelectionMode = flexSelectionByRow
End With2、有数据如下: item_no qty flag
101 4 I
103 5 G
204 4 F
111 4 I
123 5 G
141 4 I
123 5 G
204 4 I 如何通过 实现: item_no qty_F qty_G qty_I
101 4
111 4
141 4
204 4 4
103 5
123 5 5
rs.Open str, connrs, 3, 2
lastrow = 1
lastcol = 1
With grid_master
.Editable = flexEDNone
.AllowUserResizing = flexResizeBoth
.AllowUserFreezing = flexFreezeBoth
.BackColorFrozen = RGB(200, 200, 255)
.ExplorerBar = flexExMoveRows Or flexExSortShowAndMove
Set .DataSource = rs
.SelectionMode = flexSelectionByRow
End With2、有数据如下: item_no qty flag
101 4 I
103 5 G
204 4 F
111 4 I
123 5 G
141 4 I
123 5 G
204 4 I 如何通过 实现: item_no qty_F qty_G qty_I
101 4
111 4
141 4
204 4 4
103 5
123 5 5
insert into os select 1,0,'体育用品'
insert into os select 2,0,'户外运动'
insert into os select 3,1,'篮球'
insert into os select 4,1,'足球'
insert into os select 5,2,'帐篷'
insert into os select 6,2,'登山鞋'
insert into os select 7,0,'男士用品'
insert into os select 8,7,'刮胡刀'
insert into os select 9,3,'大号篮球'--求个节点下所有子节点:
create function f_cid(@id int)
returns varchar(500)
as
begin
declare @t table(id int,parentid int,desn varchar(10),lev int)
declare @lev int
set @lev=1
insert into @t select *,@lev from os where id=@id
while(@@rowcount>0)
begin
set @lev=@lev+1
insert into @t select a.*,@lev from os a,@t b
where a.parentid=b.id and b.lev=@lev-1
end
declare @cids varchar(500)
select @cids=isnull(@cids+',','')+ltrim(id) from @t order by lev
return @cids
end
go--调用函数
select *,ids=dbo.f_cid(id) from os
--得到每个节点路径:
create proc wsp2
@id int
as
select *,cast(' ' as varchar(10)) fullpath into #os from os
DECLARE @i int,@j int
set @i=0
set @j=1
select @i=max(parentid) from #os
update #os set fullpath=id
while @j<=@i
begin
update #os set fullpath=a.fullpath+','+ltrim(#os.id)
from #os inner join #os a on #os.parentid=a.id
where #os.parentid=@j
set @j=@j+1
end
select * from #os
go
--调用存储过程
exec wsp2 1
--结果
1 0 体育用品 1
2 0 户外运动 2
3 1 篮球 1,3
4 1 足球 1,4
5 2 帐篷 2,5
6 2 登山鞋 2,6
7 0 男士用品 7
8 7 刮胡刀 7,8
9 3 大号篮球 1,3,9
2、3楼的程序复杂,是否有简单代码实现以下目的:
item_no qty flag
101 4 IN
103 5 GO
204 4 FAIL
111 4 IN
123 5 FAIL
141 4 GO
123 5 GO
204 4 IN
item_no FAIL GO IN
101 4
111 4
141 4
204 4 4
103 5
123 5 5 其中 item_no、qty、flag 为原表字段名;item_no、FAIL、GO、IN 为新表字段名 。用什么语句能实现?