空值数据排序问题

时间:2022-08-10 09:45:33
         表结构如下:
ID,GSM(公司名称),TEL(电话),ADDRESS(地址),RUNMODEL(行业)zczj(注册资金)

除了ID和公司名称之外,其它的字段有可能会为空。我就是现在想把查询得到的结构进行一下处理,也就是说其中有为空字段的数据就显示在当前查询到数据的最后面,请问一下各位怎么实现呀?现在表的数据量达到了200万。先谢了。

6 个解决方案

#1


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

#2


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

#3


问下,你的为null的字段太多了,又4个。

你的排序规则是怎么定的,比只有ADDRESS为null的记录跟只有tel为null记录,谁先谁后?

还有又2个字段为null跟3个字段为null的记录,如何排序?

#4


你要是不说清楚的话,别人无法排序,因为又太多情况了。

#5


不知道是不是这个意思,不过效率肯定受影响

SQL> select * from t;

                                     ID GSM                            TEL             ADDRESS                                            RUNMODEL                                                                               ZCZJ
--------------------------------------- ------------------------------ --------------- -------------------------------------------------- -------------------------------------------------------------------------------- ----------
                                      1 a                                                                                                                                                                                  
                                      2 b                              12345                                                              23423                                                                                   100
                                      3 c                              22                                                                                                                                                  
                                      4 d                                              a                                                                                                                                          100

SQL> 
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> 

#6


引用楼主 sunwch 的回复:
        表结构如下:
ID,GSM(公司名称),TEL(电话),ADDRESS(地址),RUNMODEL(行业)zczj(注册资金)

除了ID和公司名称之外,其它的字段有可能会为空。我就是现在想把查询得到的结构进行一下处理,也就是说其中有为空字段的数据就显示在当前查询到数据的最后面,请问一下各位怎么实现呀?现在表的数据量达到了200万。先谢了。


那空字段要顺序排列的,那个空字段在前面排?

#1


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

#2


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

#3


问下,你的为null的字段太多了,又4个。

你的排序规则是怎么定的,比只有ADDRESS为null的记录跟只有tel为null记录,谁先谁后?

还有又2个字段为null跟3个字段为null的记录,如何排序?

#4


你要是不说清楚的话,别人无法排序,因为又太多情况了。

#5


不知道是不是这个意思,不过效率肯定受影响

SQL> select * from t;

                                     ID GSM                            TEL             ADDRESS                                            RUNMODEL                                                                               ZCZJ
--------------------------------------- ------------------------------ --------------- -------------------------------------------------- -------------------------------------------------------------------------------- ----------
                                      1 a                                                                                                                                                                                  
                                      2 b                              12345                                                              23423                                                                                   100
                                      3 c                              22                                                                                                                                                  
                                      4 d                                              a                                                                                                                                          100

SQL> 
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> 

#6


引用楼主 sunwch 的回复:
        表结构如下:
ID,GSM(公司名称),TEL(电话),ADDRESS(地址),RUNMODEL(行业)zczj(注册资金)

除了ID和公司名称之外,其它的字段有可能会为空。我就是现在想把查询得到的结构进行一下处理,也就是说其中有为空字段的数据就显示在当前查询到数据的最后面,请问一下各位怎么实现呀?现在表的数据量达到了200万。先谢了。


那空字段要顺序排列的,那个空字段在前面排?