我的表结构是这样的EquipmentGUID,ParentGUID,其它字段
通过 EquipmentGUID,ParentGUID将顺序记录的表形成了一个树型结构
例如
一个根节点是这样的('0001',' <root>')
底下的子节点是这样的 ('0002','0001'),('0003','0001')('0004','0001')
('0005','0002')('0006','0002')('0009','0004')('0010','0004')
我想求一条sql语句,将这颗树全部删除,各位前辈,帮帮我啊
通过 EquipmentGUID,ParentGUID将顺序记录的表形成了一个树型结构
例如
一个根节点是这样的('0001',' <root>')
底下的子节点是这样的 ('0002','0001'),('0003','0001')('0004','0001')
('0005','0002')('0006','0002')('0009','0004')('0010','0004')
我想求一条sql语句,将这颗树全部删除,各位前辈,帮帮我啊
delete tab_name where exists
(select equipmentguid from tab_name start with parentguid is null connect by prior equipmentguid=parentguid)
INSERT INTO TABLE_TREE VALUES('0002','0001','BB');
INSERT INTO TABLE_TREE VALUES('0003','0001','CC');
INSERT INTO TABLE_TREE VALUES('0004','0001','DD');
INSERT INTO TABLE_TREE VALUES('0005','0002','EE');
INSERT INTO TABLE_TREE VALUES('0006','0002','FF');
INSERT INTO TABLE_TREE VALUES('0009','0004','GG');
INSERT INTO TABLE_TREE VALUES('0010','0004','HH');INSERT INTO TABLE_TREE VALUES('0011',NULL,'AAA');
INSERT INTO TABLE_TREE VALUES('0012','0011','BBB');
COMMIT;SQL> SELECT * FROM TABLE_TREE;EQUIPMENTGUID PARENTGUID EQUIPMENTNAME
------------- ---------- -------------
0001 AA
0002 0001 BB
0003 0001 CC
0004 0001 DD
0005 0002 EE
0006 0002 FF
0009 0004 GG
0010 0004 HH
0011 AAA
0012 0011 BBB10 rows selectedSQL> SELECT * FROM TABLE_TREE T1
2 WHERE EXISTS (SELECT 1
3 FROM TABLE_TREE T2
4 WHERE T1.ROWID = T2.ROWID
5 START WITH EQUIPMENTGUID = '0002'
6 CONNECT BY PARENTGUID = PRIOR EQUIPMENTGUID)
7 ;EQUIPMENTGUID PARENTGUID EQUIPMENTNAME
------------- ---------- -------------
0002 0001 BB
0005 0002 EE
0006 0002 FFSQL> DELETE FROM TABLE_TREE T1
2 WHERE EXISTS (SELECT 1
3 FROM TABLE_TREE T2
4 WHERE T1.ROWID = T2.ROWID
5 START WITH EQUIPMENTGUID = '0002'
6 CONNECT BY PARENTGUID = PRIOR EQUIPMENTGUID)
7 ;3 rows deletedSQL> COMMIT;Commit completeSQL> SELECT * FROM TABLE_TREE;EQUIPMENTGUID PARENTGUID EQUIPMENTNAME
------------- ---------- -------------
0001 AA
0003 0001 CC
0004 0001 DD
0009 0004 GG
0010 0004 HH
0011 AAA
0012 0011 BBB7 rows selected
delete from TABLE_TREE a
where exists
(
select * from TABLE_TREE b where a.equipmentguid=b.equipmentguid
start with b.equipmentguid='0001'
CONNECT BY PARENTGUID = PRIOR EQUIPMENTGUID
)
借楼上的数据!
where exists (select *
from TABLE_TREE t
start with t.parentguid is null
connect by prior t.equipmentguid = t.parentguid)
用oracle的树型SQL来解决这个问题