这样的问题去找一本oracle sql的说明书就能解决:Hierarchical Query Examples The following CONNECT BY clause defines a hierarchical relationship in which the EMPNO value of the parent row is equal to the MGR value of the child row:
CONNECT BY PRIOR empno = mgr;
In the following CONNECT BY clause, the PRIOR operator applies only to the EMPNO value. To evaluate this condition, Oracle evaluates EMPNO values for the parent row and MGR, SAL, and COMM values for the child row:
CONNECT BY PRIOR empno = mgr AND sal > comm;
To qualify as a child row, a row must have a MGR value equal to the EMPNO value of the parent row and it must have a SAL value greater than its COMM value.
CONNECT BY PRIOR empno = mgr;
In the following CONNECT BY clause, the PRIOR operator applies only to the EMPNO value. To evaluate this condition, Oracle evaluates EMPNO values for the parent row and MGR, SAL, and COMM values for the child row:
CONNECT BY PRIOR empno = mgr AND sal > comm;
To qualify as a child row, a row must have a MGR value equal to the EMPNO value of the parent row and it must have a SAL value greater than its COMM value.
PROCEDURE Fp_Create_Tree IS
n_count number;
v_ignore number;
rec_tree recordgroup;
new_node FTREE.NODE;
item_value VARCHAR2(30);
n_cd NUMBER;
BEGIN
rec_tree := find_group('tree_km');
IF not id_null(rec_tree) THEN
delete_group(rec_tree);
END IF;
rec_tree := create_group_from_query('tree_km',
'select 1 n, level, '' '' lable,'||
'''shui'' icon, '' '' kmbm '||
'from dual ');
v_ignore := populate_group(rec_tree);
Delete_Group_Row( rec_tree, ALL_ROWS );
n_count := 1 ;
Add_Group_Row( rec_tree, END_OF_GROUP );
Set_Group_Number_Cell('tree_km.n', n_count, 1);
Set_Group_Number_Cell('tree_km.level', n_count, 1);
Set_Group_Char_Cell( 'tree_km.lable', n_count, '所有科目');
Set_Group_Char_Cell( 'tree_km.icon', n_count, 'shui');
Set_Group_Char_Cell( 'tree_km.kmbm', n_count, '0');
n_count := n_count + 1;
FOR c IN (select mc,dm from dm_mx where dmm = '1200' order by dm) LOOP
Add_Group_Row( rec_tree, END_OF_GROUP );
Set_Group_Number_Cell('tree_km.n', n_count, 1);
Set_Group_Number_Cell('tree_km.level', n_count, 2);
Set_Group_Char_Cell( 'tree_km.lable', n_count, c.mc);
Set_Group_Char_Cell( 'tree_km.icon', n_count, 'shui');
Set_Group_Char_Cell( 'tree_km.kmbm', n_count, c.dm);
n_count := n_count + 1;
FOR r in (select kmbm||' '||kmmc lable, kmbm,dp_cw_kmbm.f_get_kmjb(kmbm)+2 kmjb
from dm_kmbm where substr(kmbm,1,1) = c.dm
order by kmbm) LOOP
Add_Group_Row( rec_tree, END_OF_GROUP );
Set_Group_Number_Cell('tree_km.n', n_count, 1);
Set_Group_Number_Cell('tree_km.level', n_count, r.kmjb);
Set_Group_Char_Cell( 'tree_km.lable', n_count, r.lable);
Set_Group_Char_Cell( 'tree_km.icon', n_count, 'shui');
Set_Group_Char_Cell( 'tree_km.kmbm', n_count, r.kmbm);
n_count := n_count + 1;
END LOOP ;
END LOOP;
Add_Group_Row( rec_tree, END_OF_GROUP );
Set_Group_Number_Cell('tree_km.n', n_count, 1);
Set_Group_Number_Cell('tree_km.level', n_count, 2);
Set_Group_Char_Cell( 'tree_km.lable', n_count, '往来科目');
Set_Group_Char_Cell( 'tree_km.icon', n_count, 'shui');
Set_Group_Char_Cell( 'tree_km.kmbm', n_count, 6);
n_count := n_count + 1;
FOR c IN (select mc,dm from dm_mx where dmm = '1200' order by dm) LOOP
Add_Group_Row( rec_tree, END_OF_GROUP );
Set_Group_Number_Cell('tree_km.n', n_count, 1);
Set_Group_Number_Cell('tree_km.level', n_count, 3);
Set_Group_Char_Cell( 'tree_km.lable', n_count, c.mc);
Set_Group_Char_Cell( 'tree_km.icon', n_count, 'shui');
Set_Group_Char_Cell( 'tree_km.kmbm', n_count, c.dm);
n_count := n_count + 1;
FOR r in (select kmbm||' '||kmmc lable, kmbm,4 kmjb
from dm_kmbm where substr(kmbm,1,1) = c.dm and wllx='1'
order by kmbm) LOOP
Add_Group_Row( rec_tree, END_OF_GROUP );
Set_Group_Number_Cell('tree_km.n', n_count, 1);
Set_Group_Number_Cell('tree_km.level', n_count, r.kmjb);
Set_Group_Char_Cell( 'tree_km.lable', n_count, r.lable);
Set_Group_Char_Cell( 'tree_km.icon', n_count, 'shui');
Set_Group_Char_Cell( 'tree_km.kmbm', n_count, r.kmbm);
n_count := n_count + 1;
END LOOP ;
END LOOP;
ftree.set_tree_property('tree_km.tree1', ftree.record_group, rec_tree);
VAR.P_NODE := FTREE.FIND_TREE_NODE('tree_km.tree1','往来科目', Ftree.FIND_NEXT,
Ftree.NODE_LABEL, Ftree.ROOT_NODE, Ftree.ROOT_NODE);END Fp_Create_Tree;查询DECLARE
htree ITEM;
parent_node FTREE.NODE;
parent_label VARCHAR2(100);
tree_node FTREE.NODE;
v_where VARCHAR2(50):='1=1';
BEGIN
htree := Find_Item('tree_km.tree1');
parent_node := Ftree.Get_Tree_Node_Property(htree, :SYSTEM.TRIGGER_NODE,Ftree.node_value);
parent_label := Ftree.Get_Tree_Node_Property(htree, :SYSTEM.TRIGGER_NODE,Ftree.node_label);
VAR.P_KMBM := parent_node;
VAR.P_KMMC := Dp_cw_kmbm.F_Get_Kmmc(parent_node);
--std.show_msg(FTREE.GET_TREE_SELECTION(HTREE,1));
IF FTREE.GET_TREE_SELECTION(HTREE,1) >= VAR.P_NODE THEN
VAR.PAR := 1;
show_view('can_kmye_wl');
hide_view('can_kmye');
v_where:=v_where||' AND kmbm = '||chr(39)||parent_node||chr(39);
Set_Block_Property('v_kmye_wl',Default_Where,V_Where);
Std.Do_Query('v_kmye_wl');
--如果为非往来科目则不能插入记录
IF dp_cw_kmbm.f_get_sfwl(parent_node) = 0 THEN
Set_Block_Property('v_kmye_wl',INSERT_ALLOWED,PROPERTY_TRUE);
ELSE
Set_Block_Property('v_kmye_wl',INSERT_ALLOWED,PROPERTY_FALSE);
END IF;
ELSE
VAR.PAR := 0;
show_view('can_kmye');
hide_view('can_kmye_wl');
v_where:=v_where||' AND kmbm LIKE '||chr(39)||parent_node||'%'||chr(39);
Set_Block_Property('v_kmye',Default_Where,V_Where);
Std.Do_Query('v_kmye');
END IF;
EXCEPTION
WHEN OTHERS THEN
Std.Exception_Others;
END;
from PCATEGORY c1
START WITH c1.PNAME = 'root'
CONNECT BY PRIOR c1.NAME = c1.PNAME;
SELECT JGBH,JGMC,SJJGBH FROM T_JG START WITH JGBH='1070000000'
CONNECT BY SJJGBH=PRIOR JGBH
JGBH (机构编号),JGMC(机构名称),SJJGBH(上级机构编号)
你可以试一试,把对应的字段改掉就可以了,看看查询出的结果你就明白了
procedure TForm1.Button2Click(Sender: TObject);
var
anitem1, anitem2, anitem3: TTreeNode;
str1, str2, str3: string;
TreeView2, TreeView3: TTreeView;
begin
TreeView2 := TTreeView.Create(self);
TreeView2.Parent := Form1;
TreeView3 := TTreeView.Create(self);
TreeView3.Parent := Form1;
TreeView2.Visible := false;
TreeView3.Visible := false;
query1.Close;
query1.sql.clear;
query1.SQL.add('SELECT JGBH,JGMC,SJJGBH FROM T_JG START WITH JGBH=''1070000000'''
+ 'CONNECT BY SJJGBH=PRIOR JGBH ');
query1.open; TreeView1.Items.Clear;
TreeView2.Items.Clear;
TreeView3.Items.Clear;
anitem1 := TreeView1.Items.Add(nil, query1.fieldbyname('jgmc').asstring + '[' + query1.FieldByName('jgbh').asstring + ']');
anitem2 := TreeView2.Items.Add(nil, query1.fieldbyname('jgbh').asstring);
anitem3 := TreeView3.Items.Add(nil, query1.fieldbyname('sjjgbh').asstring); while not query1.Eof do
begin
query1.next;
str1 := query1.FieldByName('jgmc').asstring + '[' + query1.FieldByName('jgbh').asstring + ']';
str2 := query1.FieldByName('jgbh').asstring;
str3 := query1.FieldByName('sjjgbh').asstring;
if str3 = anitem2.text then //¸¸×Ó¹Øϵ
begin
anitem1 := TreeView1.Items.Addchild(anitem1, str1);
anitem2 := TreeView2.Items.Addchild(anitem2, str2);
anitem3 := TreeView3.Items.Addchild(anitem3, str3);
end
else
if str3 = anitem3.text then //ÐֵܹØϵ
begin
anitem1 := TreeView1.Items.Add(anitem1, str1);
anitem2 := TreeView2.Items.Add(anitem2, str2);
anitem3 := TreeView3.Items.Add(anitem3, str3);
end
else //²»ÊÇֱϵÇ×Êô
begin
while anitem1.parent <> nil do
begin
anitem1 := anitem1.parent;
anitem2 := anitem2.parent;
anitem3 := anitem3.parent;
if str3 = anitem3.text then //ÐֵܹØϵ
begin
anitem1 := TreeView1.Items.Add(anitem1, str1);
anitem2 := TreeView2.Items.Add(anitem2, str2);
anitem3 := TreeView3.Items.Add(anitem3, str3);
break;
end
end; end;
end;
TreeView2.Free;
TreeView3.Free;
end;
比如:table(field1 primary key ,field2)
field1 field2
1 2
2 4
3 1
4 3
这样1,2,3,4 条记录我就知道是一组的,现在怎么把这一组给查出来啊
如果查到第一条,我就知道第二条也是该组的,依次类推
select lpad(' ',2*level)||Operation||' '||Options||' '||Object_Name q_plan
From Plan_Table
Where statement_id='TEST'
Connect by prior id = Parent_id
and Statement_id = 'TEST'
Start With Id = 1;
from table1
connect by prior field1 = field2
start with field1 = 1;