5,388
社区成员
发帖
与我相关
我的任务
分享
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')
with cte as
(
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
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
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' )
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;
for i:=0 to tv.items.count-1 do
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;
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;
ln:=$d$a;
{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});就是完整的树了
[Qty] [dbo].[dtQuantity] NOT NULL
以上[dbo].[dtQuantity]为自定义类型,可直接为[Qty] [nvarchar](50) NOT NULL,