SELECT ELM_CD, ELM_NM FROM ( SELECT '1' rn, --给结果集MM和NN加个常量字段 AA.COMPANY_CD, AA.ELM_CD, AA.ELM_NM, AA.ELM_OBJ_FLG, CC.USER_SORT FROM ELEMENT_INFO AA, COMPANY_INFO BB, USER_INFO CC WHERE AA.COMPANY_CD = BB.COMPANY_CD AND AA.COMPANY_CD = CC.COMPANY_CD AND AA.USER_CD = CC.USER_CD AND AA.ELM_OBJ_FLG = '0 ' AND AA.ELM_OBJ = '事务对象 ' UNION ...... //此处省去很多UNION的对象,区别是检索TABLE变化和WHERE条件不同 SELECT AA.COMPANY_CD, AA.ELM_CD, AA.ELM_NM, AA.ELM_OBJ_FLG, CC.USER_SORT FROM ELEMENT_INFO AA, COMPANY_INFO BB, USER_INFO CC WHERE AA.COMPANY_CD = BB.COMPANY_CD AND AA.COMPANY_CD = CC.COMPANY_CD AND AA.USER_CD = CC.USER_CD AND AA.ELM_OBJ_FLG = '4 ' AND AA.ELM_OBJ LIKE '%事务对象% ' ) MM, ( SELECT '2' rn,COMPANY_CD FROM COMPANY_INFO WHERE COMPANY_STSTUS = '1 ' START WITH FATHER_COMPANY_CD IS NULL CONNECT BY PRIOR COMPANY_CD = FATHER_COMPANY_CD ORDER BY COMPANY_SORT, COMPANY_NM ) NN WHERE MM.COMPANY_CD = NN.COMPANY_CD ORDER BY rn,--加个按rn排序 MM.ELM_OBJ_FLG, --事务分类0,1,2,3,4 MM.USER_SORT --用户特定排序
你的外层的order by 打乱了你里层的order by ,实际上在你程序里ORDER BY COMPANY_SORT, COMPANY_NM 没起到任何作用,并不是你说的COMPANY_SORT这个下排序,最终的结果的只有MM.ELM_OBJ_FLG,MM.USER_SORT 起到了排序的作用,你可以尝试把外面的order by 改成( SELECT COMPANY_CD ,COMPANY_SORT, COMPANY_NM FROM COMPANY_INFO WHERE COMPANY_STSTUS = '1 ' START WITH FATHER_COMPANY_CD IS NULL CONNECT BY PRIOR COMPANY_CD = FATHER_COMPANY_CD ORDER BY COMPANY_SORT, COMPANY_NM ) NN WHERE MM.COMPANY_CD = NN.COMPANY_CD ORDER BY NN.COMPANY_SORT, NN.COMPANY_NM, MM.ELM_OBJ_FLG, --事务分类0,1,2,3,4 MM.USER_SORT --用户特定排序
前面列出的是用到的3张表及一些数据,后面是我根据根据需求写出的SQL,排序不对,求教
ELM_NM
FROM ( SELECT '1' rn, --给结果集MM和NN加个常量字段
AA.COMPANY_CD,
AA.ELM_CD,
AA.ELM_NM,
AA.ELM_OBJ_FLG,
CC.USER_SORT
FROM ELEMENT_INFO AA,
COMPANY_INFO BB,
USER_INFO CC
WHERE AA.COMPANY_CD = BB.COMPANY_CD AND
AA.COMPANY_CD = CC.COMPANY_CD AND
AA.USER_CD = CC.USER_CD AND
AA.ELM_OBJ_FLG = '0 ' AND
AA.ELM_OBJ = '事务对象 '
UNION
...... //此处省去很多UNION的对象,区别是检索TABLE变化和WHERE条件不同 SELECT AA.COMPANY_CD,
AA.ELM_CD,
AA.ELM_NM,
AA.ELM_OBJ_FLG,
CC.USER_SORT
FROM ELEMENT_INFO AA,
COMPANY_INFO BB,
USER_INFO CC
WHERE AA.COMPANY_CD = BB.COMPANY_CD AND
AA.COMPANY_CD = CC.COMPANY_CD AND
AA.USER_CD = CC.USER_CD AND
AA.ELM_OBJ_FLG = '4 ' AND
AA.ELM_OBJ LIKE '%事务对象% '
) MM,
( SELECT '2' rn,COMPANY_CD
FROM COMPANY_INFO
WHERE COMPANY_STSTUS = '1 '
START WITH FATHER_COMPANY_CD IS NULL
CONNECT BY PRIOR COMPANY_CD = FATHER_COMPANY_CD
ORDER BY COMPANY_SORT,
COMPANY_NM
) NN
WHERE MM.COMPANY_CD = NN.COMPANY_CD
ORDER BY rn,--加个按rn排序 MM.ELM_OBJ_FLG, --事务分类0,1,2,3,4
MM.USER_SORT --用户特定排序
| |---------ELM1112
| |---------ELM1113
|---|USER12|-----ELM1121
| |---------ELM1122
| |---------ELM1123
|---|USER13|-----ELM1131
| |---------ELM1132
| |---------ELM1133COMPANY2|---|USER21|-----ELM2211
| |---------ELM2212
| |---------ELM12213
|---|USER22|-----ELM2221
| |---------ELM12222
| |---------ELM2223
|---|USER23|-----ELM2231
| |---------ELM2232
| |---------ELM2233
|USER2
|USER1
|USER1
特别感谢zhuomingwang的回答,明天到公司继续验证正确性,同时非常感谢Oraclefans_的回复
COMPANY_NM 没起到任何作用,并不是你说的COMPANY_SORT这个下排序,最终的结果的只有MM.ELM_OBJ_FLG,MM.USER_SORT 起到了排序的作用,你可以尝试把外面的order by 改成( SELECT COMPANY_CD ,COMPANY_SORT, COMPANY_NM FROM COMPANY_INFO
WHERE COMPANY_STSTUS = '1 '
START WITH FATHER_COMPANY_CD IS NULL
CONNECT BY PRIOR COMPANY_CD = FATHER_COMPANY_CD
ORDER BY COMPANY_SORT,
COMPANY_NM
) NN
WHERE MM.COMPANY_CD = NN.COMPANY_CD
ORDER BY NN.COMPANY_SORT, NN.COMPANY_NM,
MM.ELM_OBJ_FLG, --事务分类0,1,2,3,4
MM.USER_SORT --用户特定排序