oracle 父子排序,父子项排序 求大神们出手

本帖最后由 260600sz 于 2016-8-26 23:04 编辑

父子项排序

with tb as

(

--省级

select 'e' subcode,'e'parentcode,'湖北' AREA,'SO' TYPE,10 QTY FROM DUAL UNION ALL

select 'e' subcode,'e'parentcode,'湖北' AREA,'INV' TYPE,10 QTY FROM DUAL UNION ALL----

select 'e' subcode,'e'parentcode,'湖北' AREA,'BOUND' TYPE,10 QTY FROM DUAL UNION ALL

--地市

select 'c' subcode,'e'parentcode,'武汉' AREA,'SO' TYPE,10 QTY FROM DUAL UNION ALL

select 'c' subcode,'e'parentcode,'武汉' AREA,'INV' TYPE,20 QTY FROM DUAL UNION ALL----

select 'c' subcode,'e'parentcode,'武汉' AREA,'BOUND' TYPE,10 QTY FROM DUAL UNION ALL

--地市

select 'd' subcode,'e'parentcode,'汉口' AREA,'SO' TYPE,10 QTY FROM DUAL UNION ALL

select 'd' subcode,'e'parentcode,'汉口' AREA,'INV' TYPE,30 QTY FROM DUAL UNION ALL----

select 'd' subcode,'e'parentcode,'汉口' AREA,'BOUND' TYPE,20 QTY FROM DUAL UNION ALL

--省份

select 'a' subcode,'a'parentcode,'广东' AREA,'SO' TYPE,10 QTY FROM DUAL UNION ALL

select 'a' subcode,'a'parentcode,'广东' AREA,'INV' TYPE,60 QTY FROM DUAL UNION ALL----

select 'a' subcode,'a'parentcode,'广东' AREA,'BOUND' TYPE,10 QTY FROM DUAL UNION ALL

--地市

select 'h' subcode,'a'parentcode,'广州' AREA,'SO' TYPE,10 QTY FROM DUAL UNION ALL

select 'h' subcode,'a'parentcode,'广州' AREA,'INV' TYPE,20 QTY FROM DUAL UNION ALL----

select 'h' subcode,'a'parentcode,'广州' AREA,'BOUND' TYPE,10 QTY FROM DUAL UNION ALL

--地市

select 'g' subcode,'a'parentcode,'深圳' AREA,'SO' TYPE,10 QTY FROM DUAL UNION ALL

select 'g' subcode,'a'parentcode,'深圳' AREA,'INV' TYPE,30 QTY FROM DUAL UNION ALL----

select 'g' subcode,'a'parentcode,'深圳' AREA,'BOUND' TYPE,20 QTY FROM DUAL

)

select * from tb;

要求

--按 省级 TYPE类型的INV库存数量 从大到小排序(广东INV=60 大于湖北INV=10)，再按 地市的TYPE类型的INV库存数量 从大到小排序(深圳INV 30大于广州INV 20)；

但 地市级要紧接在省份后面

效果如下；

with tb1 as

(

--省份

select 'a' subcode,'a'parentcode,'广东' AREA,'SO' TYPE,10 QTY FROM DUAL UNION ALL

select 'a' subcode,'a'parentcode,'广东' AREA,'INV' TYPE,60 QTY FROM DUAL UNION ALL----

select 'a' subcode,'a'parentcode,'广东' AREA,'BOUND' TYPE,10 QTY FROM DUAL UNION ALL

--地市

select 'g' subcode,'a'parentcode,'深圳' AREA,'SO' TYPE,10 QTY FROM DUAL UNION ALL

select 'g' subcode,'a'parentcode,'深圳' AREA,'INV' TYPE,30 QTY FROM DUAL UNION ALL----

select 'g' ubcode,'a'parentcode,'深圳' AREA,'BOUND' TYPE,20 QTY FROM DUAL UNION ALL

--地市

select 'h' subcode,'a'parentcode,'广州' AREA,'SO' TYPE,10 QTY FROM DUAL UNION ALL

select 'h' subcode,'a'parentcode,'广州' AREA,'INV' TYPE,20 QTY FROM DUAL UNION ALL----

select 'h' subcode,'a'parentcode,'广州' AREA,'BOUND' TYPE,10 QTY FROM DUAL UNION ALL

--省级

select 'e' subcode,'e'parentcode,'湖北' AREA,'SO' TYPE,10 QTY FROM DUAL UNION ALL

select 'e' subcode,'e'parentcode,'湖北' AREA,'INV' TYPE,10 QTY FROM DUAL UNION ALL----

select 'e' subcode,'e'parentcode,'湖北' AREA,'BOUND' TYPE,10 QTY FROM DUAL UNION ALL

--地市

select 'd' subcode,'e'parentcode,'汉口' AREA,'SO' TYPE,10 QTY FROM DUAL UNION ALL

select 'd' subcode,'e'parentcode,'汉口' AREA,'INV' TYPE,30 QTY FROM DUAL UNION ALL----

select 'd' subcode,'e'parentcode,'汉口' AREA,'BOUND' TYPE,20 QTY FROM DUAL UNION ALL

--地市

select 'c' subcode,'e'parentcode,'武汉' AREA,'SO' TYPE,10 QTY FROM DUAL UNION ALL

select 'c' subcode,'e'parentcode,'武汉' AREA,'INV' TYPE,20 QTY FROM DUAL UNION ALL----

select 'c' subcode,'e'parentcode,'武汉' AREA,'BOUND' TYPE,10 QTY FROM DUAL

)

select * from tb1;效果就是运行tb1的结果