一共四个表:
表一Punit: Pid Pname 第一层树
表二UUnit: Uid Pid Uname 第二层树
表三Iunit: Iid Uid Iname 第三层树
表四Lunit: Id Iid text 第四层树
他们的关系是一层包括一层的关系,UUnit表里面的PID对应Punit表里面的pid在UUnit表里面有很多Pid对应,根据对应的pid查找出相应的子树。一层一层的关系一共四层。
表一Punit: Pid Pname 第一层树
表二UUnit: Uid Pid Uname 第二层树
表三Iunit: Iid Uid Iname 第三层树
表四Lunit: Id Iid text 第四层树
他们的关系是一层包括一层的关系,UUnit表里面的PID对应Punit表里面的pid在UUnit表里面有很多Pid对应,根据对应的pid查找出相应的子树。一层一层的关系一共四层。
var L1_node, L2_node, L3_node, L4_node : TTreeNode;
strNodeNameL1 : string;
strNodeNameL2 : string;
strNodeNameL3 : string;
strNodeNameL4 : string;
begin
intBunruiCode:=0;
intBunruiKB:=0;
with qrySelect do begin
DatabaseName:=gDbName;
strSql:='';
strSql:=strSql + 'SELECT A.NACDC2,A.NACDNM,B.DNM001,B.DNM002,C.CNM002,C.CNM003,';
strSql:=strSql + 'D.CN2003,D.CN2004 ';
strSql:=strSql + 'FROM NMNAMEP A LEFT JOIN DNAMEP B ON A.NACDC2=B.DNM1001 ';
strSql:=strSql + 'LEFT JOIN CNAMEP C ON B.DNM001=C.CNM001 ';
strSql:=strSql + 'LEFT JOIN CNAME2P D ON C.CNM001=D.CN2001 AND C.CNM002=D.CN2002 ';
strSql:=strSql + 'WHERE A.NACDKB=''082'' ';
strSql:=strSql + 'ORDER BY A.NACDC2,B.DNM001,C.CNM002,D.CN2003 ';
Close;
SQL.Clear;
SQL.Add(strSql);
GF_WriteLog('BunruiKensaku',strSql);
Open;
while Not Eof do begin
//大分類一桁目
if strNodeNameL1 <> FieldByName('NACDC2').AsString then begin
strNodeNameL1 := FieldByName('NACDC2').AsString;
L1_node := TreeView1.Items.Add(nil, FieldByName('NACDNM').AsString);
strNodeNameL2:='';
strNodeNameL3:='';
strNodeNameL4:='';
end;
//大分類
if (strNodeNameL2 <> FieldByName('DNM001').AsString) and (trim(FieldByName('DNM001').AsString)<>'0')
and (trim(FieldByName('DNM001').AsString)<>'') then begin
strNodeNameL2 := FieldByName('DNM001').AsString;
L2_node := TreeView1.Items.AddChild(L1_node, FieldByName('DNM002').AsString);
strNodeNameL3:='';
strNodeNameL4:='';
end;
//中分類
if (strNodeNameL3 <> FieldByName('CNM002').AsString) and (trim(FieldByName('CNM002').AsString)<>'0')
and (trim(FieldByName('CNM002').AsString)<>'') then begin
strNodeNameL3 := FieldByName('CNM002').AsString;
L3_node := TreeView1.Items.AddChild(L2_node, FieldByName('CNM003').AsString);
strNodeNameL4:='';
end;
//小分類
if (strNodeNameL4<> FieldByName('CN2003').AsString) and (trim(FieldByName('CN2003').AsString)<>'0')
and (trim(FieldByName('CN2003').AsString)<>'') then begin
strNodeNameL4:= FieldByName('CN2003').AsString;
L4_node := TreeView1.Items.AddChild(L3_node, FieldByName('CN2004').AsString);
end;
Next;
end;
end;
end;
如果必须用四个表可以用嵌套查询试试;
譬如var TN,TN1,TN2,Tn3: Ttreenode;
begin
with adoquery1 do
begin
close;
sql.clear;
sql.text := 'select distinct pid from table1 where ...';
open;
while not eof do
begin
TN := treeview1.items.add(nil,fieldbyname('pid').asstring);
with adoquery2 do
begin
close;
sql.clear;
sql.text := 'select distinct uid from table2 where ...';
open;
while not eof do
begin
TN1 := treeview1.items.addchild(TN,fieldbyname('uid').asstring);
with adoquery3 do
begin
//类似的操作;不再写了;
end;
next;
end;
end;
next;
end;
end;
end;