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

ksdy138141314 2015-08-04 12:15:22

数据如上图所示,如要的结果如下图,比如用户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'));
...全文
214 6 打赏 收藏 转发到动态 举报
写回复
6 条回复
切换为时间正序
请发表友善的回复…
发表回复
ksdy138141314 2015-08-05
  • 打赏
  • 举报
回复
引用 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个支节点的数据也不少。还没想好如何设计。
小灰狼W 2015-08-05
  • 打赏
  • 举报
回复
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 2015-08-05
  • 打赏
  • 举报
回复
引用 3 楼 wildwave 的回复:
这个设计看起来不怎么合理,几个ID很容易出现矛盾。另外你的例子结果中的上级用户,其实是指顶级用户,那么,和下面的节点怎么判断区域呢,如果中间出现了一次左,两次右,那么,属于左还是右
每个用户都会是上级用户,我这里只用了一个用户举例,因为每个用户有2个下级,左边一个下级及其所有的下级属于左,右边一个下级及其所有的下级属于右。 现在这个关系表还没定,还在考虑怎么设计这个关系表。
小灰狼W 2015-08-05
  • 打赏
  • 举报
回复
这个设计看起来不怎么合理,几个ID很容易出现矛盾。另外你的例子结果中的上级用户,其实是指顶级用户,那么,和下面的节点怎么判断区域呢,如果中间出现了一次左,两次右,那么,属于左还是右
ksdy138141314 2015-08-05
  • 打赏
  • 举报
回复
顶一个,让大家看到
ksdy138141314 2015-08-04
  • 打赏
  • 举报
回复
太长了。没人看么
相关推荐

3,479

社区成员

发帖
与我相关
我的任务
社区描述
Oracle 高级技术相关讨论专区
社区管理员
  • 高级技术社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告