Oracle排序问题

Question

假设表A有以下字段:
id, key1 ,key2, key3, key4, key5, key6, type
id和type不能为空,其他均可为空!!
查询条件是:
当type="B"时:
1.若key1和key有值时,按key1 升序,  key2 升序排序
2.若key5有值时,按key5升序排序
说明:key为key1+key2+key3
请问这个SQL该如何写

Answer 1

你说得不是很清楚,我试着写下,你看看:
SELECT *
  FROM (SELECT id,
               key1,
               key2,
               key3,
               key4,
               key5,
               key6,
               TYPE decode(TYPE, 'B', nvl(key1, nvl(key2, key3)), key1) orderkey
          FROM tablename) t
 ORDER BY orderkey DESC;

Answer 2

都有值按3排就这样:SELECT *
  FROM (SELECT id,
               key1,
               key2,
               key3,
               key4,
               key5,
               key6,
               TYPE decode(TYPE, 'B', decode(key1, NULL, decode(key2, NULL, key3), key3), key3) orderkey
          FROM tablename) t
 ORDER BY orderkey DESC;

Answer 3

select * from tableA where type='B' order by decode(key1*key*key5,
       0,
       decode(key1*key,
              0,
              decode(key5,
                     0,
                     1,
                     key5),
              key1 || ',' || key),
       key2 || ',' || key3 || ',' || key4)
;