oracle 递归sql的写法求解！！！！！

[ksdy138141314]

数据如上图所示，如要的结果如下图，比如用户qiji888对于用户lixiang来说，所在的位置在lixiang的第2级，位于左侧（也就是图中的区域，0表示左，1表示右），层级关系大家应该都好理解，那这个区域如何理解？？比如lixiang用户有leftuid和rightuid，在leftuid下的所有用户，包括子用户都属于左侧，rightuid下的子用户都属于右侧，现在这个区域不知道有没有什么好的算法实现？？？
-------------希望得到的结果


这里的区域如何算？？？？？求大神支支招！！！！！
select
'lixiang' as "上级用户",
leftuid AS "下级用户",
level as "层次",
0 as "区域"
from test_date
where level <= 6
and leftuid is not null
start with userid='lixiang'
connect by prior userid =topuid;
-------------创建测试表和数据
create table TEST_DATE
(
id NUMBER,
userid VARCHAR2(20),
topuid VARCHAR2(20),
leftuid VARCHAR2(20),
rightuid VARCHAR2(20),
tjuid VARCHAR2(20),
memberlevel NUMBER(1),
addtime DATE,
orderprice NUMBER
);

insert into test_date (USERID, TOPUID, ORDERPRICE, LEFTUID, RIGHTUID, TJUID, ADDTIME)
values ('lixiang', 'lee2010', 30000, 'chuangzao', 'cassie', 'mtm1000', to_date('08-10-2010 00:11:00', 'dd-mm-yyyy hh24:mi:ss'));

insert into test_date (USERID, TOPUID, ORDERPRICE, LEFTUID, RIGHTUID, TJUID, ADDTIME)
values ('chuangzao', 'lixiang', 30000, 'qiji888', 'Ceci', 'lixiang', to_date('08-10-2010 00:13:00', 'dd-mm-yyyy hh24:mi:ss'));

insert into test_date (USERID, TOPUID, ORDERPRICE, LEFTUID, RIGHTUID, TJUID, ADDTIME)
values ('cassie', 'lixiang', 500, 'david', null, 'lixiang', to_date('25-07-2013 02:38:00', 'dd-mm-yyyy hh24:mi:ss'));

insert into test_date (USERID, TOPUID, ORDERPRICE, LEFTUID, RIGHTUID, TJUID, ADDTIME)
values ('david', 'cassie', 500, 'sarah', null, 'lixiang', to_date('25-07-2013 02:38:00', 'dd-mm-yyyy hh24:mi:ss'));

insert into test_date (USERID, TOPUID, ORDERPRICE, LEFTUID, RIGHTUID, TJUID, ADDTIME)
values ('qiji888', 'chuangzao', 30000, 'chuangxing', 'Alexandra', 'chuangzao', to_date('08-10-2010 00:15:00', 'dd-mm-yyyy hh24:mi:ss'));

insert into test_date (USERID, TOPUID, ORDERPRICE, LEFTUID, RIGHTUID, TJUID, ADDTIME)
values ('Ceci', 'chuangzao', 500, 'Kade', null, 'chuangzao', to_date('25-07-2013 02:33:00', 'dd-mm-yyyy hh24:mi:ss'));

insert into test_date (USERID, TOPUID, ORDERPRICE, LEFTUID, RIGHTUID, TJUID, ADDTIME)
values ('sarah', 'david', 500, 'jasminey', null, 'lixiang', to_date('25-07-2013 02:39:00', 'dd-mm-yyyy hh24:mi:ss'));

insert into test_date (USERID, TOPUID, ORDERPRICE, LEFTUID, RIGHTUID, TJUID, ADDTIME)
values ('Alexandra', 'qiji888', 500, 'Kathryn', null, 'qiji888', to_date('25-07-2013 02:28:00', 'dd-mm-yyyy hh24:mi:ss'));

insert into test_date (USERID, TOPUID, ORDERPRICE, LEFTUID, RIGHTUID, TJUID, ADDTIME)
values ('chuangxing', 'qiji888', 30000, 'chuangye', 'Lola', 'qiji888', to_date('08-10-2010 00:16:00', 'dd-mm-yyyy hh24:mi:ss'));

insert into test_date (USERID, TOPUID, ORDERPRICE, LEFTUID, RIGHTUID, TJUID, ADDTIME)
values ('Kade', 'Ceci', 500, 'Kellen', null, 'chuangzao', to_date('25-07-2013 02:33:00', 'dd-mm-yyyy hh24:mi:ss'));

insert into test_date (USERID, TOPUID, ORDERPRICE, LEFTUID, RIGHTUID, TJUID, ADDTIME)
values ('Lola', 'chuangxing', 500, 'Carly', null, 'chuangxing', to_date('23-07-2013 23:39:00', 'dd-mm-yyyy hh24:mi:ss'));

insert into test_date (USERID, TOPUID, ORDERPRICE, LEFTUID, RIGHTUID, TJUID, ADDTIME)
values ('Kellen', 'Kade', 500, 'Randy', null, 'chuangzao', to_date('25-07-2013 02:34:00', 'dd-mm-yyyy hh24:mi:ss'));

insert into test_date (USERID, TOPUID, ORDERPRICE, LEFTUID, RIGHTUID, TJUID, ADDTIME)
values ('jasminey', 'sarah', 500, 'janice', null, 'lixiang', to_date('25-07-2013 02:39:00', 'dd-mm-yyyy hh24:mi:ss'));

insert into test_date (USERID, TOPUID, ORDERPRICE, LEFTUID, RIGHTUID, TJUID, ADDTIME)
values ('chuangye', 'chuangxing', 30000, 'zhuoyue', 'Andre', 'chuangxing', to_date('08-10-2010 00:17:00', 'dd-mm-yyyy hh24:mi:ss'));

insert into test_date (USERID, TOPUID, ORDERPRICE, LEFTUID, RIGHTUID, TJUID, ADDTIME)
values ('Kathryn', 'Alexandra', 500, 'Karter', null, 'qiji888', to_date('25-07-2013 02:28:00', 'dd-mm-yyyy hh24:mi:ss'));

insert into test_date (USERID, TOPUID, ORDERPRICE, LEFTUID, RIGHTUID, TJUID, ADDTIME)
values ('Karter', 'Kathryn', 500, 'Issac', null, 'qiji888', to_date('25-07-2013 02:29:00', 'dd-mm-yyyy hh24:mi:ss'));

insert into test_date (USERID, TOPUID, ORDERPRICE, LEFTUID, RIGHTUID, TJUID, ADDTIME)
values ('Randy', 'Kellen', 500, 'Casta', null, 'chuangzao', to_date('25-07-2013 02:34:00', 'dd-mm-yyyy hh24:mi:ss'));

insert into test_date (USERID, TOPUID, ORDERPRICE, LEFTUID, RIGHTUID, TJUID, ADDTIME)
values ('zhuoyue', 'chuangye', 30000, 'daiyan', 'magnoliaz', 'chuangye', to_date('08-10-2010 00:17:00', 'dd-mm-yyyy hh24:mi:ss'));

insert into test_date (USERID, TOPUID, ORDERPRICE, LEFTUID, RIGHTUID, TJUID, ADDTIME)
values ('janice', 'jasminey', 500, null, null, 'lixiang', to_date('25-07-2013 02:41:00', 'dd-mm-yyyy hh24:mi:ss'));

insert into test_date (USERID, TOPUID, ORDERPRICE, LEFTUID, RIGHTUID, TJUID, ADDTIME)
values ('Andre', 'chuangye', 500, 'Andrew', null, 'chuangye', to_date('23-07-2013 22:56:00', 'dd-mm-yyyy hh24:mi:ss'));

insert into test_date (USERID, TOPUID, ORDERPRICE, LEFTUID, RIGHTUID, TJUID, ADDTIME)
values ('Carly', 'Lola', 500, 'Binga', null, 'chuangxing', to_date('23-07-2013 23:39:00', 'dd-mm-yyyy hh24:mi:ss'));

insert into test_date (USERID, TOPUID, ORDERPRICE, LEFTUID, RIGHTUID, TJUID, ADDTIME)
values ('magnoliaz', 'zhuoyue', 500, 'carnationa', null, 'zhuoyue', to_date('23-07-2013 22:35:00', 'dd-mm-yyyy hh24:mi:ss'));

insert into test_date (USERID, TOPUID, ORDERPRICE, LEFTUID, RIGHTUID, TJUID, ADDTIME)
values ('Casta', 'Randy', 500, null, null, 'chuangzao', to_date('25-07-2013 02:35:00', 'dd-mm-yyyy hh24:mi:ss'));

insert into test_date (USERID, TOPUID, ORDERPRICE, LEFTUID, RIGHTUID, TJUID, ADDTIME)
values ('Binga', 'Carly', 500, 'Isla', null, 'chuangxing', to_date('23-07-2013 23:42:00', 'dd-mm-yyyy hh24:mi:ss'));

insert into test_date (USERID, TOPUID, ORDERPRICE, LEFTUID, RIGHTUID, TJUID, ADDTIME)
values ('Andrew', 'Andre', 500, 'Andy', null, 'chuangye', to_date('23-07-2013 22:58:00', 'dd-mm-yyyy hh24:mi:ss'));

insert into test_date (USERID, TOPUID, ORDERPRICE, LEFTUID, RIGHTUID, TJUID, ADDTIME)
values ('Issac', 'Karter', 500, 'Mckenna', null, 'qiji888', to_date('25-07-2013 02:29:00', 'dd-mm-yyyy hh24:mi:ss'));

insert into test_date (USERID, TOPUID, ORDERPRICE, LEFTUID, RIGHTUID, TJUID, ADDTIME)
values ('daiyan', 'zhuoyue', 30000, 'vip2011', 'YIYI100', 'zhuoyue', to_date('08-10-2010 00:18:00', 'dd-mm-yyyy hh24:mi:ss'));

[ksdy138141314]

太长了。没人看么

[ksdy138141314]

顶一个，让大家看到

[小灰狼W]

这个设计看起来不怎么合理，几个ID很容易出现矛盾。另外你的例子结果中的上级用户，其实是指顶级用户，那么，和下面的节点怎么判断区域呢，如果中间出现了一次左，两次右，那么，属于左还是右

[ksdy138141314]

引用 3 楼 wildwave 的回复:

这个设计看起来不怎么合理，几个ID很容易出现矛盾。另外你的例子结果中的上级用户，其实是指顶级用户，那么，和下面的节点怎么判断区域呢，如果中间出现了一次左，两次右，那么，属于左还是右

每个用户都会是上级用户，我这里只用了一个用户举例，因为每个用户有2个下级，左边一个下级及其所有的下级属于左，右边一个下级及其所有的下级属于右。 现在这个关系表还没定，还在考虑怎么设计这个关系表。

[小灰狼W]

SELECT CONNECT_BY_ROOT TOPUID 上级用户, USERID 下级用户, LEVEL 层次, CASE WHEN (SELECT X.LEFTUID FROM TEST_DATE X WHERE X.USERID = 'lixiang') = CONNECT_BY_ROOT T.USERID THEN 0 WHEN (SELECT X.RIGHTUID FROM TEST_DATE X WHERE X.USERID = 'lixiang') = CONNECT_BY_ROOT T.USERID THEN 1 END 区域 FROM TEST_DATE T START WITH T.TOPUID = 'lixiang' CONNECT BY PRIOR T.USERID = T.TOPUID 还是觉得表结构设计应该变下。例如位置为左还是右，放在子节点的一个属性字段上就好了

[ksdy138141314]

引用 5 楼 wildwave 的回复:

SELECT CONNECT_BY_ROOT TOPUID 上级用户, USERID 下级用户, LEVEL 层次, CASE WHEN (SELECT X.LEFTUID FROM TEST_DATE X WHERE X.USERID = 'lixiang') = CONNECT_BY_ROOT T.USERID THEN 0 WHEN (SELECT X.RIGHTUID FROM TEST_DATE X WHERE X.USERID = 'lixiang') = CONNECT_BY_ROOT T.USERID THEN 1 END 区域 FROM TEST_DATE T START WITH T.TOPUID = 'lixiang' CONNECT BY PRIOR T.USERID = T.TOPUID 还是觉得表结构设计应该变下。例如位置为左还是右，放在子节点的一个属性字段上就好了

感谢哥们，这个关系表我也认为要改一下，但目前没想到好的方案，按照目前设计来看的话，按照用户100W来看，关系表的数据量还是不小了，起码最顶点就有100W条，第二层的2个支节点的数据也不少。还没想好如何设计。