
数据如上图所示,如要的结果如下图,比如用户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'));