新手求教:新建一个窗体,拖一个edit,button,输入001016133,如何用递归生成BOM树?
以下是测试数据,delphi SQL+TreeView递归如何生成BOM树?(求高人)CREATE TABLE [dbo].[tb_bom](
[Parn_litm] [nvarchar](50) NOT NULL,
[Itemno] [nvarchar](50) NULL,
[Chld_litm] [nvarchar](50) NOT NULL,
[Qty] [dbo].[dtQuantity] NOT NULL
) ON [PRIMARY]
insert into tb_bom values( '001016133', '001', 'X40004A10ZN', '2.0000' )
insert into tb_bom values( '001016133', '002', 'X91026A10ZN', '3.0000' )
insert into tb_bom values( '001016133', '003', 'X91035A10ZN1', '1.0000' )
insert into tb_bom values( '001016133', '004', 'A99060A10ZN', '1.0000' )
insert into tb_bom values( '001016133', '005', 'X22010A10NI', '1.0000' )
insert into tb_bom values( '001016133', '006', 'X39000A', '1.0000' )
insert into tb_bom values( '001016133', '007', 'S32008A00CU', '1.0000' )
insert into tb_bom values( '001016133', '008', 'X91027A12ZN', '1.0000' )
insert into tb_bom values( '001016133', '009', 'M30504D10NI', '1.0000' )
insert into tb_bom values( '001016133', '010', 'X99085A', '2.0000' )
insert into tb_bom values( '001016133', '011', 'A10895A10NI', '1.0000' )
insert into tb_bom values( '001016133', '012', 'M60289A10NI', '1.0000' )
insert into tb_bom values( '001016133', '013', 'X33112A10NI', '3.0000' )
insert into tb_bom values( '001016133', '014', 'S12329K00016', '1.0000' )
insert into tb_bom values( '001016133', '015', 'Y8001VO130', '1.0000' )
insert into tb_bom values( '001016133', '016', 'Y00005A', '1.0000' )
insert into tb_bom values( '001016133', '017', 'Y50005A', '1.0000' )
insert into tb_bom values( '001016133', '018', 'Y10010A', '1.0000' )
insert into tb_bom values( '001016133', '019', 'Y30001A', '1.0000' )
insert into tb_bom values( '001016133', '020', 'BBA001016133', '1.0000' )
insert into tb_bom values( '001016133', '021', 'BCA001016133', '12.0000')
以下是测试数据,delphi SQL+TreeView递归如何生成BOM树?(求高人)CREATE TABLE [dbo].[tb_bom](
[Parn_litm] [nvarchar](50) NOT NULL,
[Itemno] [nvarchar](50) NULL,
[Chld_litm] [nvarchar](50) NOT NULL,
[Qty] [dbo].[dtQuantity] NOT NULL
) ON [PRIMARY]
insert into tb_bom values( '001016133', '001', 'X40004A10ZN', '2.0000' )
insert into tb_bom values( '001016133', '002', 'X91026A10ZN', '3.0000' )
insert into tb_bom values( '001016133', '003', 'X91035A10ZN1', '1.0000' )
insert into tb_bom values( '001016133', '004', 'A99060A10ZN', '1.0000' )
insert into tb_bom values( '001016133', '005', 'X22010A10NI', '1.0000' )
insert into tb_bom values( '001016133', '006', 'X39000A', '1.0000' )
insert into tb_bom values( '001016133', '007', 'S32008A00CU', '1.0000' )
insert into tb_bom values( '001016133', '008', 'X91027A12ZN', '1.0000' )
insert into tb_bom values( '001016133', '009', 'M30504D10NI', '1.0000' )
insert into tb_bom values( '001016133', '010', 'X99085A', '2.0000' )
insert into tb_bom values( '001016133', '011', 'A10895A10NI', '1.0000' )
insert into tb_bom values( '001016133', '012', 'M60289A10NI', '1.0000' )
insert into tb_bom values( '001016133', '013', 'X33112A10NI', '3.0000' )
insert into tb_bom values( '001016133', '014', 'S12329K00016', '1.0000' )
insert into tb_bom values( '001016133', '015', 'Y8001VO130', '1.0000' )
insert into tb_bom values( '001016133', '016', 'Y00005A', '1.0000' )
insert into tb_bom values( '001016133', '017', 'Y50005A', '1.0000' )
insert into tb_bom values( '001016133', '018', 'Y10010A', '1.0000' )
insert into tb_bom values( '001016133', '019', 'Y30001A', '1.0000' )
insert into tb_bom values( '001016133', '020', 'BBA001016133', '1.0000' )
insert into tb_bom values( '001016133', '021', 'BCA001016133', '12.0000')
解决方案 »
- 调用自定义函数问题:
- 高分求解:webbrowser为何不能得到url地址
- 每次提问都得到大家解决,希望这次还能得到指点。100分!!!! 不够可以再加。 只要能解决问题!!!!
- 这里问问题真好~先谢谢大家。一个小问题马上给分~也许大家都曾经遇到过的问题~~
- 大家看看这个数据库怎么设计合理,请指教!
- 在线等,解决马上给分!Delphi写限时版的软件?
- 小菜问题,分全送了
- 怎样使用TQurry删除旧的表根据查询创建新的表!不够再加!(在线等候)
- 求求你们了?
- 文件合并器原理是什么
- 请问下adoquery查出的数据如何用字符串数组获取
- 在delphi中如何快捷复制一个窗口(包括窗体里的所有组件及相应程序)
[Qty] [dbo].[dtQuantity] NOT NULL
以上[dbo].[dtQuantity]为自定义类型,可直接为[Qty] [nvarchar](50) NOT NULL,
1、sql就使用cte(sql2005开始支持)先递归得到完整的子树
2、全部可能相关的记录加载到本地内存记录集,程序反复循环扫描内存记录集生成完整的子树
一般层次表的结构都是:id,parn_id两个字段(本节点,父节点)即可构建树形
你的除了itemno,Parn_litm,还有一个子节点(Chld_litm)字段?另外,事例记录也尚未体现多级的关系。
对不起各位大侠:可能是我没有表达清楚,上面的整合记录可忽略,请以下面5笔数据为例,parn_litm为主节点,chld_litm为子节点,诚请各位大侠指点迷津,delphi+TreeView,在edit1输入'001016133',在treeview中用递归生成并展开bom表?以下为测试数据,小弟求各位指导求教:
CREATE TABLE [dbo].[tb_bom](
[Parn_litm] [nvarchar](50) NOT NULL,
[Chld_litm] [nvarchar](50) NOT NULL,
[Qty] [nvarchar](50) NOT NULL
) ON [PRIMARY]
insert into tb_bom values( '001000333', 'X22641A00NI', '1.0000' )
insert into tb_bom values( 'X22641A00NI', 'X22641C00NI', '1.0000' )
insert into tb_bom values( 'X22641A00NI', 'X39005A10NI', '1.0000' )
insert into tb_bom values( 'X22641C00NI', 'X22641C00RW', '1.0000' )
insert into tb_bom values( 'X39005A10NI', 'X39005A10RW', '1.0000' )
declare @t TABLE (
[Parn_litm] [nvarchar](50) NOT NULL,
[Chld_litm] [nvarchar](50) NOT NULL,
[Qty] [nvarchar](50) NOT NULL
);
insert into @t values( '001000333', 'X22641A00NI', '1.0000' )
insert into @t values( 'X22641A00NI', 'X22641C00NI', '1.0000' )
insert into @t values( 'X22641A00NI', 'X39005A10NI', '1.0000' )
insert into @t values( 'X22641C00NI', 'X22641C00RW', '1.0000' )
insert into @t values( 'X39005A10NI', 'X39005A10RW', '1.0000' )
insert into @t values( '101000333', 'X22641A00NI', '1.0000' )
select * from @t;with cte as
(
select * from @t where [Parn_litm]='001000333'
union all
select b.* from cte a
join @t b on a.[Chld_litm]=b.[Parn_litm]
)
select * from cte
(
select *,1 flevel from @t where [Parn_litm]='001000333'
union all
select b.*,a.flevel+1 from cte a
join @t b on a.[Chld_litm]=b.[Parn_litm]
--where [Parn_litm]=
)
select * from cte
开效果,敬请指教!如下图,如解决,另开贴给分或加分,小弟新手,求教!
{adoquery}q.sql.text:=
'with cte as'
+ln+'('
+ln+'select *,1 flevel,cast([Chld_litm] as nvarchar(4000)) fo from @t where [Parn_litm]='+quotedstr(edit1.text)
+ln+'union all'
+ln+'select b.*,a.flevel+1,fo+''\''+b.[Chld_litm] from cte a '
+ln+'join @t b on a.[Chld_litm]=b.[Parn_litm]'
+ln+')'
+ln+'select * from cte'
+ln+'order by fo';
q.open;
s:='';
while not q.eof do
begin
s:=s+stringofchar(' ',q.fieldbyname('flevel').asinteger-1)+q.fieldbyname('Chld_litm').asstring
q.next;
end;然后让treeview.loadfromstream(stringstream{s});就是完整的树了
type
PTNodeInfo=^TNodeInfo;
TNodeInfo=record
parn_litm,chld_litm:string;
end;
procedure Treceiving_slip_frm.CreateTree;
procedure LoadTree(Pre:string;preNode:TTreeNode);
var
pInfo:PTNodeInfo;
node:TTreeNode;
sql:string;
Query:TADOQUery;
begin
Query:=TADOQuery.Create(nil);
Query.ConnectionString:=sys_datamoudle_Frm.adoq_connect.ConnectionString;
sql:= Format('select parn_litm,chld_litm from bom_loc where parn_litm=%s', [QuotedStr(pre)]);
Query.Close;
Query.SQL.Clear;
Query.SQL.Add(sql);
Query.Open;
ShowMessage(Query.SQL.Text);
Query.First;
while not Query.Eof do
begin
try
new(pInfo);
pInfo.parn_litm:=Query.Fields[0].AsString;
pInfo.chld_litm:=Query.Fields[1].AsString;
node:=treeview1.Items.AddChild(preNode,pInfo.parn_litm);
node.Data:=pInfo;
loadTree(pInfo.parn_litm,node);
Query.Next;
finally
Query.Close;
Query.Free;
end;
end;
end;
begin
LoadTree(Edit1.text,nil);
treeview1.FullExpand;
end;
procedure Treceiving_slip_frm.Button1Click(Sender: TObject);
var
T:dword;
begin
inherited;
lab_dt.Caption := Format('createTree所用时间: %d ms', [GetTickCount - T]);
//调用递归
CreateTree;
end;
我说的方法2是它的优化版本,sql只需要发一次如果死循环了,可能是数据有问题
可以在.AddChild前扫描现有的树节点,是否已经有它了,有则不再添加
if PTNodeInfo(tv.items[i].data).chld_litm:=Query.Fields[1].AsString then
begin
query.next;
continue;
end;
然后:
try
new(pInfo);
pInfo.parn_litm:=Query.Fields[0].AsString;
pInfo.chld_litm:=Query.Fields[1].AsString;
如原图:
procedure Treceiving_slip_frm.CreateTree;
procedure LoadTree(Pre:string;preNode:TTreeNode);
var
pInfo:PTNodeInfo;
node:TTreeNode;
sql:string;
i:integer;
Query:TADOQUery;
begin
Application.ProcessMessages;
Query:=TADOQuery.Create(nil);
Query.ConnectionString:=sys_datamoudle_Frm.adoq_connect.ConnectionString;
sql:= Format('select parn_litm,chld_litm from bom_loc where parn_litm=%s', [QuotedStr(pre)]);
Query.Close;
Query.SQL.Clear;
Query.SQL.Add(sql);
Query.Open;
Query.First;
while not Query.Eof do
begin
{for i:=0 to treeview1.items.count-1 do
if PTNodeInfo(treeview1.items[i].data).chld_litm=Query.Fields[1].AsString then
begin
query.next;
continue;
end; }
try
new(pInfo);
pInfo.parn_litm:=Query.Fields[0].AsString;
pInfo.chld_litm:=Query.Fields[1].AsString;
node:=treeview1.Items.AddChild(preNode,pInfo.parn_litm);
node.Data:=pInfo;
Application.ProcessMessages;
loadTree(pInfo.chld_litm,node);
Query.Next;
finally
Query.Close;
Query.Free;
end;
end;
end;
begin
if treeview1.HasParent then
begin
treeview1.Items.Clear;
treeview1.Items.BeginUpdate;
LoadTree(Edit1.text,nil);
treeview1.FullExpand;
treeview1.Items.EndUpdate;
end;
end;
是指多次sql了?18楼的代码好像也没防止死循环(互为父节点)的地方?