上面的写错了select tb.* from tbname tb,( select distinct parent_id id from tbname where parent_id<>0 minus select distinct id from tbname ) t where t.id=tb.id(+);
select tb.* from tbname tb,( select distinct id from tbname minus select distinct parent_id id from tbname where parent_id <>0 ) t where t.id=tb.id(+);
select * from tbname t where id not in (select parent_id from tbname) union select * from tbname t where parent_id<>0
select t.* from ttree t start with ID is not null and parent_id=0 connect by prior id=parent_id;
select * from test t where parent_id=0 and not exists ( select 1 from test tt where tt.parent_id=t.id ) union select * from test t where parent_id<>0 and exists ( select 1 from test tt where t.parent_id=tt.id )
用自身关联查询:select t1.* from table1 t1, table1 t2 where t1.id<>t2.parent_id
错了,应该是 select t1.* from table1 t1, table1 t2 where t1.id = t2.parent_id
select t1.* from table1 t1, table1 t2 where (t1.id = t2.parent_id) or t1.parent_id=0
SELECT * FROM tbname AS t WHERE t.id not in ( select t1.id from tbname t1,tbname t2 where t1.id=t2.parent_id );
我这个绝对真确: 我遇到过同你一样的问题 select * from tbname connect by prior id = parent_id start with parent-id = '0';
select distinct parent_id id from tbname
where parent_id<>0
minus
select distinct id from tbname
) t
where t.id=tb.id(+);
bzszp(www.bzszp.533.net)
你用的Exists写的是正好是我的答案,后面一个却查找不到记录
003 它是没有子项的啊,在parent_id中没有一项是003 不就表示003 没有子项么
select distinct id from tbname
minus
select distinct parent_id id from tbname
where parent_id <>0
) t
where t.id=tb.id(+);
union
select * from tbname t where parent_id<>0
也就是说如果id 有子类,则子类显示在它的下面,没有别的要求001 电子类 0
004 电子电容 001
005 电子电阻 001
002 金属类 0
006 有色金属 002
007 无色金属 002
003 橡胶类 0
connect by prior id=parent_id;
你提供的这个语句好像多了四条记录,结果是
001 电子类 0
004 电子电容 001
005 电子电阻 001
002 金属类 0
006 有色金属 002
007 无色金属 002
003 橡胶类 0
004 电子电容 001
005 电子电阻 001
006 有色金属 002
007 无色金属 002
start with ID is not null and parent_id=0
connect by prior id=parent_id;
(
select 1 from test tt where tt.parent_id=t.id
)
union
select * from test t where parent_id<>0 and exists
(
select 1 from test tt where t.parent_id=tt.id
)
where t1.id<>t2.parent_id
select t1.* from table1 t1, table1 t2
where t1.id = t2.parent_id
where (t1.id = t2.parent_id) or t1.parent_id=0
FROM tbname AS t
WHERE t.id not in
(
select t1.id from tbname t1,tbname t2
where t1.id=t2.parent_id
);
我遇到过同你一样的问题
select * from tbname connect by prior id = parent_id start with parent-id = '0';