create table RMS_APP_NAV
(
NAVIGATION_ID VARCHAR2(255 CHAR) not null,
APP_NAVIGATION_ID VARCHAR2(255 CHAR),
NAVIGATION_NAME VARCHAR2(255 CHAR),
NAVIGATION_URL VARCHAR2(255 CHAR),
NAV_TYPE VARCHAR2(255 CHAR),
REMARK VARCHAR2(255 CHAR),
ORDERCOLU NUMBER(10)
);
alter table RMS_APP_NAV
add primary key (NAVIGATION_ID);
insert into RMS_APP_NAV (NAVIGATION_ID, APP_NAVIGATION_ID, NAVIGATION_NAME, NAVIGATION_URL, NAV_TYPE, REMARK, ORDERCOLU)
values ('APP0', null, '人民出版社', '/', '0', '' || chr(9) || '' || chr(9) || '' || chr(9) || '' || chr(9) || '' || chr(9) || '' || chr(9) || '' || chr(9) || '' || chr(9) || '' || chr(9) || '' || chr(9) || '' || chr(10) || '' || chr(9) || '' || chr(9) || '' || chr(9) || '' || chr(9) || '' || chr(9) || '' || chr(9) || '' || chr(9) || '' || chr(9) || '' || chr(9) || '', 0);
insert into RMS_APP_NAV (NAVIGATION_ID, APP_NAVIGATION_ID, NAVIGATION_NAME, NAVIGATION_URL, NAV_TYPE, REMARK, ORDERCOLU)
values ('APP099005', 'APP099', '退款管理', 'backend/p_book/goodsRefund.jsp', '0', null, 5);
insert into RMS_APP_NAV (NAVIGATION_ID, APP_NAVIGATION_ID, NAVIGATION_NAME, NAVIGATION_URL, NAV_TYPE, REMARK, ORDERCOLU)
values ('APP099006', 'APP099', '退货管理', 'backend/p_book/goodsRejected.jsp', '0', null, 6);
insert into RMS_APP_NAV (NAVIGATION_ID, APP_NAVIGATION_ID, NAVIGATION_NAME, NAVIGATION_URL, NAV_TYPE, REMARK, ORDERCOLU)
values ('APP097001', 'APP097', '配送方式管理', 'backend/deliver/deliver!findDelivers.do', '0', null, 1);
insert into RMS_APP_NAV (NAVIGATION_ID, APP_NAVIGATION_ID, NAVIGATION_NAME, NAVIGATION_URL, NAV_TYPE, REMARK, ORDERCOLU)
values ('APP097002', 'APP097', '支付方式管理', 'backend/payment/payment!getPaymentsList.do?operate=list', '0', null, 2);
insert into RMS_APP_NAV (NAVIGATION_ID, APP_NAVIGATION_ID, NAVIGATION_NAME, NAVIGATION_URL, NAV_TYPE, REMARK, ORDERCOLU)
values ('APP095', 'APP0', '系统管理', '/', '0', null, 5);
insert into RMS_APP_NAV (NAVIGATION_ID, APP_NAVIGATION_ID, NAVIGATION_NAME, NAVIGATION_URL, NAV_TYPE, REMARK, ORDERCOLU)
values ('APP094', 'APP0', '统一权限管理', '/', '0', null, 6);
insert into RMS_APP_NAV (NAVIGATION_ID, APP_NAVIGATION_ID, NAVIGATION_NAME, NAVIGATION_URL, NAV_TYPE, REMARK, ORDERCOLU)
values ('APP094002', 'APP094', '机构管理', 'backend/p_manage/group/group_list.jsp', '0', null, 2);
insert into RMS_APP_NAV (NAVIGATION_ID, APP_NAVIGATION_ID, NAVIGATION_NAME, NAVIGATION_URL, NAV_TYPE, REMARK, ORDERCOLU)
values ('APP094003', 'APP094', '人员管理', 'backend/p_manage/admin/admin_list.jsp', '0', null, 3);
insert into RMS_APP_NAV (NAVIGATION_ID, APP_NAVIGATION_ID, NAVIGATION_NAME, NAVIGATION_URL, NAV_TYPE, REMARK, ORDERCOLU)
values ('APP098010', 'APP098', '数量加权', 'backend/printbookexp/getAllAgioInfo.do', '0', null, 9);
insert into RMS_APP_NAV (NAVIGATION_ID, APP_NAVIGATION_ID, NAVIGATION_NAME, NAVIGATION_URL, NAV_TYPE, REMARK, ORDERCOLU)
values ('APP098011', 'APP098', '印刷规格', 'backend/specifications/getAllSpecificati.do', '0', null, 10);
values ('APP099', 'APP0', '纸书销售管理', '/', '0', '' || chr(9) || '' || chr(9) || '' || chr(9) || '' || chr(9) || '' || chr(9) || '' || chr(9) || '' || chr(9) || '' || chr(9) || '' || chr(9) || '' || chr(9) || '' || chr(13) || '' || chr(10) || '' || chr(9) || '' || chr(9) || '' || chr(9) || '' || chr(9) || '' || chr(9) || '' || chr(9) || '' || chr(9) || '' || chr(9) || '' || chr(9) || '', 1);
insert into RMS_APP_NAV (NAVIGATION_ID, APP_NAVIGATION_ID, NAVIGATION_NAME, NAVIGATION_URL, NAV_TYPE, REMARK, ORDERCOLU)
values ('APP098002', 'APP098', '财务审核', 'backend/p_reqprint/waitVerifyPrintOrder.jsp', '0', null, 2);
insert into RMS_APP_NAV (NAVIGATION_ID, APP_NAVIGATION_ID, NAVIGATION_NAME, NAVIGATION_URL, NAV_TYPE, REMARK, ORDERCOLU)
values ('APP098003', 'APP098', '发货管理', 'backend/p_reqprint/goodsDeliver.jsp', '0', null, 3);
insert into RMS_APP_NAV (NAVIGATION_ID, APP_NAVIGATION_ID, NAVIGATION_NAME, NAVIGATION_URL, NAV_TYPE, REMARK, ORDERCOLU)
values ('APP098007', 'APP098', '换货管理', 'backend/p_reqprint/exchangesOrders.jsp', '0', null, 7);
insert into RMS_APP_NAV (NAVIGATION_ID, APP_NAVIGATION_ID, NAVIGATION_NAME, NAVIGATION_URL, NAV_TYPE, REMARK, ORDERCOLU)
values ('APP098008', 'APP098', '图书印刷信息', 'backend/p_reqprint/bookext/book_search.jsp', '0', null, 8);
commit;create table RMS_ROLE_APP
(
NAVIGATION_ID VARCHAR2(255 CHAR) not null,
ROLE_ID VARCHAR2(255 CHAR) not null
);
alter table RMS_ROLE_APP
add primary key (ROLE_ID, NAVIGATION_ID);
insert into RMS_ROLE_APP (NAVIGATION_ID, ROLE_ID)
values ('APP0', 'ff8080812c4efbb2012c53613853002e');
insert into RMS_ROLE_APP (NAVIGATION_ID, ROLE_ID)
values ('APP094', 'ff8080812c4efbb2012c53613853002e');
insert into RMS_ROLE_APP (NAVIGATION_ID, ROLE_ID)
values ('APP094001', 'ff8080812c4efbb2012c53613853002e');
insert into RMS_ROLE_APP (NAVIGATION_ID, ROLE_ID)
values ('APP094002', 'ff8080812c4efbb2012c53613853002e');
insert into RMS_ROLE_APP (NAVIGATION_ID, ROLE_ID)
values ('APP094003', 'ff8080812c4efbb2012c53613853002e');
insert into RMS_ROLE_APP (NAVIGATION_ID, ROLE_ID)
values ('APP094004', 'ff8080812c4efbb2012c53613853002e');
insert into RMS_ROLE_APP (NAVIGATION_ID, ROLE_ID)
values ('APP095', 'ff8080812c4efbb2012c53613853002e');
insert into RMS_ROLE_APP (NAVIGATION_ID, ROLE_ID)
values ('APP095002', 'ff8080812c4efbb2012c53613853002e');
insert into RMS_ROLE_APP (NAVIGATION_ID, ROLE_ID)
values ('APP096', 'ff8080812c4efbb2012c53613853002e');
insert into RMS_ROLE_APP (NAVIGATION_ID, ROLE_ID)
values ('APP096001', 'ff8080812c4efbb2012c53613853002e');
insert into RMS_ROLE_APP (NAVIGATION_ID, ROLE_ID)
values ('APP096002', 'ff8080812c4efbb2012c53613853002e');
insert into RMS_ROLE_APP (NAVIGATION_ID, ROLE_ID)
values ('APP097', 'ff8080812c4efbb2012c53613853002e');
insert into RMS_ROLE_APP (NAVIGATION_ID, ROLE_ID)
values ('APP097001', 'ff8080812c4efbb2012c53613853002e');
insert into RMS_ROLE_APP (NAVIGATION_ID, ROLE_ID)
values ('APP097002', 'ff8080812c4efbb2012c53613853002e');
insert into RMS_ROLE_APP (NAVIGATION_ID, ROLE_ID)
values ('APP098', 'ff8080812c4efbb2012c53613853002e');
insert into RMS_ROLE_APP (NAVIGATION_ID, ROLE_ID)
values ('APP098001', 'ff8080812c4efbb2012c53613853002e');
insert into RMS_ROLE_APP (NAVIGATION_ID, ROLE_ID)
values ('APP098002', 'ff8080812c4efbb2012c53613853002e');
insert into RMS_ROLE_APP (NAVIGATION_ID, ROLE_ID)
values ('APP098003', 'ff8080812c4efbb2012c53613853002e');
insert into RMS_ROLE_APP (NAVIGATION_ID, ROLE_ID)
values ('APP098004', 'ff8080812c4efbb2012c53613853002e');
insert into RMS_ROLE_APP (NAVIGATION_ID, ROLE_ID)
values ('APP098005', 'ff8080812c4efbb2012c53613853002e');
insert into RMS_ROLE_APP (NAVIGATION_ID, ROLE_ID)
values ('APP098006', 'ff8080812c4efbb2012c53613853002e');
insert into RMS_ROLE_APP (NAVIGATION_ID, ROLE_ID)
values ('APP098007', 'ff8080812c4efbb2012c53613853002e');
insert into RMS_ROLE_APP (NAVIGATION_ID, ROLE_ID)
values ('APP098008', 'ff8080812c4efbb2012c53613853002e');
insert into RMS_ROLE_APP (NAVIGATION_ID, ROLE_ID)
values ('APP098009', 'ff8080812c4efbb2012c53613853002e');
insert into RMS_ROLE_APP (NAVIGATION_ID, ROLE_ID)
values ('APP098010', 'ff8080812c4efbb2012c53613853002e');
insert into RMS_ROLE_APP (NAVIGATION_ID, ROLE_ID)
values ('APP098011', 'ff8080812c4efbb2012c53613853002e');
insert into RMS_ROLE_APP (NAVIGATION_ID, ROLE_ID)
values ('APP099', 'ff8080812c4efbb2012c53613853002e');
insert into RMS_ROLE_APP (NAVIGATION_ID, ROLE_ID)
values ('APP0990002', 'ff8080812c4efbb2012c53613853002e');
insert into RMS_ROLE_APP (NAVIGATION_ID, ROLE_ID)
values ('APP099001', 'ff8080812c4efbb2012c53613853002e');
insert into RMS_ROLE_APP (NAVIGATION_ID, ROLE_ID)
values ('APP099003', 'ff8080812c4efbb2012c53613853002e');
insert into RMS_ROLE_APP (NAVIGATION_ID, ROLE_ID)
values ('APP099004', 'ff8080812c4efbb2012c53613853002e');
insert into RMS_ROLE_APP (NAVIGATION_ID, ROLE_ID)
values ('APP099005', 'ff8080812c4efbb2012c53613853002e');
insert into RMS_ROLE_APP (NAVIGATION_ID, ROLE_ID)
values ('APP099006', 'ff8080812c4efbb2012c53613853002e');
insert into RMS_ROLE_APP (NAVIGATION_ID, ROLE_ID)
values ('APP099007', 'ff8080812c4efbb2012c53613853002e');
insert into RMS_ROLE_APP (NAVIGATION_ID, ROLE_ID)
values ('APP099008', 'ff8080812c4efbb2012c53613853002e');
insert into RMS_ROLE_APP (NAVIGATION_ID, ROLE_ID)
values ('APP099010', 'ff8080812c4efbb2012c53613853002e');
commit;
--下面是我的查询语句
select a.*
from (select *
from (select *
from rms_app_nav n
connect by prior navigation_id = app_navigation_id
start with navigation_id = 'APP0'
order siblings by ordercolu)
where app_navigation_id is not null) a,
(select t1.*
from rms_app_nav t1, rms_role_app t2
where t2.navigation_id = t1.navigation_id
and t2.role_id = 'ff8080812c4efbb2012c53613853002e'
and t1.app_navigation_id is not null) b
where a.navigation_id = b.navigation_id;
为什么查询出的顺序是和b的顺序一样的,我想让排序顺序和a的一样。
解决方案 »
- ORA-00922: 缺少或无效选项
- 如何在proc程序中把sql_cursor型的变量作为函数参数进行传递??在线等.....
- oracle 含非法字符的变量如何保存到数据表里
- 八千万数据,库和表的设计问题
- 问存储过程调用的问题,谢谢大家,在线等
- TNS-12154 TNS:could not resolve service name
- SID 的问题---值得一看
- [oracle9i] 请问如何用一句SQL语句获得如下结果?
- 删除数据库之前忘了删除例程了,怎么再删除这些例程?
- 我们公司要订阅报刊,我想定一份关于数据库?
- linux下运行netca和dbca报错
- 新手写存储过程,提示执行成功,但是没有新表产生
可以是: ORDER BY 最终的字段名顺序(倒序要带 DESC)
或者 ORDER BY 最终的字段顺序号
你可以指定hint方式来强制指定驱动表。
如果是hash join,则顺序是未知的。
如果要保证顺序,order by是正确的方法。
from (select *
from (select *
from rms_app_nav n
connect by prior navigation_id = app_navigation_id
start with navigation_id = 'APP0'
order siblings by ordercolu)
where app_navigation_id is not null) a,
(select t1.*
from rms_app_nav t1, rms_role_app t2
where t2.navigation_id = t1.navigation_id
and t2.role_id = 'ff8080812c4efbb2012c53613853002e'
and t1.app_navigation_id is not null) b
where a.navigation_id = b.navigation_id order by a.navigation_id;
select *
from (select *
from (select *
from rms_app_nav n
connect by prior navigation_id = app_navigation_id
start with navigation_id = 'APP0'
order siblings by ordercolu)
where app_navigation_id is not null) a
where exists (select *
from (select t1.*
from rms_app_nav t1, rms_role_app t2
where t2.navigation_id = t1.navigation_id
and t2.role_id = 'ff8080812c4efbb2012c53613853002e'
and t1.app_navigation_id is not null) b
where a.navigation_id = b.navigation_id)
exists会和前面的表的顺序保持一致