是的,我大概写了一下 就是按照表B的order字段从1 - 6 排序。我自己试着写了个select A.key1,A,Col1,A.Col2 form A ,( select key1 , 1 as index orderNo ,order1 as order from B union select key1 , 2 as index orderNo ,order2 as order from B union select key1 , 3 as index orderNo ,order3 as order from B union select key1 , 4 as index orderNo ,order4 as order from B union select key1 , 5 as index orderNo ,order5 as order from B union select key1 , 6 as index orderNo ,order6 as order from B ) BB where A.key1 = BB.key1 (+) A.Col2 = BB.order(+) order By BB.orderNo ,A.Col1
楼主 上面的sql 好像就可以
SELECT * FROM (SELECT A.KEY, A.COL1 , A.COL2 , (CASE A.COL2 WHEN B.ORDERS1 THEN 1 WHEN B.ORDERS2 THEN 2 WHEN B.ORDERS3 THEN 3 WHEN B.ORDERS4 THEN 4 WHEN B.ORDERS5 THEN 5 WHEN B.ORDERS6 THEN 6 ELSE 99 END) RN FROM TEST_A A, TEST_B B WHERE A.KEY = B.KEY) C ORDER BY C.KEY, C.RN, C.COL1 是不是简单点
form A ,(
select key1 , 1 as index orderNo ,order1 as order from B
union
select key1 , 2 as index orderNo ,order2 as order from B
union
select key1 , 3 as index orderNo ,order3 as order from B
union
select key1 , 4 as index orderNo ,order4 as order from B
union
select key1 , 5 as index orderNo ,order5 as order from B
union
select key1 , 6 as index orderNo ,order6 as order from B
) BB
where A.key1 = BB.key1 (+)
A.Col2 = BB.order(+)
order By BB.orderNo ,A.Col1
FROM (SELECT A.KEY,
A.COL1 ,
A.COL2 ,
(CASE A.COL2
WHEN B.ORDERS1 THEN
1
WHEN B.ORDERS2 THEN
2
WHEN B.ORDERS3 THEN
3
WHEN B.ORDERS4 THEN
4
WHEN B.ORDERS5 THEN
5
WHEN B.ORDERS6 THEN
6
ELSE
99
END) RN
FROM TEST_A A, TEST_B B
WHERE A.KEY = B.KEY) C
ORDER BY C.KEY, C.RN, C.COL1
是不是简单点