空值数据排序问题

Question

select *
  from tb
order by 3 nulls last,4 nulls last,5 nulls last,6 nulls last
这里表示把第3个字段为空的排到最后，依次第4个字段为空的排到最后，

Answer 1

SELECT ID,GSM,TEL,ADDRESS,RUNMODEL,zczj FROM tb ORDER BY ID,GSM,TEL NULLS LAST,ADDRESS NULLS LAST,RUNMODEL NULLS LAST,zczj NULLS LAST

Answer 2

问下，你的为null的字段太多了，又4个。你的排序规则是怎么定的，比只有ADDRESS为null的记录跟只有tel为null记录，谁先谁后？还有又2个字段为null跟3个字段为null的记录，如何排序？

Answer 3

不知道是不是这个意思，不过效率肯定受影响SQL> select * from t;                                     ID GSM                            TEL             ADDRESS                                            RUNMODEL                                                                               ZCZJ
--------------------------------------- ------------------------------ --------------- -------------------------------------------------- -------------------------------------------------------------------------------- ----------
                                      1 a                                                                                                                                                                                  
                                      2 b                              12345                                                              23423                                                                                   100
                                      3 c                              22                                                                                                                                                  
                                      4 d                                              a                                                                                                                                          100SQL> 
SQL> select id,gsm,tel,address,runmodel,zczj from (
  2    select id,gsm,tel,address,runmodel,zczj,
  3           decode(tel,null,0,1)+decode(address,null,0,1)+decode(RUNMODEL,null,0,1)+decode(zczj,null,0,1) ord
  4           from t
  5  ) order by ord desc
  6  ;                                     ID GSM                            TEL             ADDRESS                                            RUNMODEL                                                                               ZCZJ
--------------------------------------- ------------------------------ --------------- -------------------------------------------------- -------------------------------------------------------------------------------- ----------
                                      2 b                              12345                                                              23423                                                                                   100
                                      4 d                                              a                                                                                                                                          100
                                      3 c                              22                                                                                                                                                  
                                      1 a                                                                                                                                                                                  SQL>