一个排序的问题！！！！！！！！！！！！！！！！！！！！！！！！！！！！

Question

一个排序的问题，表tb中有五列信息，id,name,ord1,ord2,ord3,现在想根据后三列进行一个排序，就是如果ord1,ord2,ord3都有信息则排在前面
如果其中任何一列没有信息，则排在下面，如果任两列没有信息，则再往下排，都没有信息的排在最后，这要怎么实现啊？

Answer 1

order by case when (ord1 is not null) and (ord2 is not null) and (ord3 is not null) then 0
  when (ord1 is null) or (ord2 is null) or (ord3 is null) then 1
  when (ord1 is null and ord2 is null) or (ord1 is null and ord3 is null) or (ord2 is null and ord3 is null) then 2
  when (ord1 is null) and (ord2 is null) and (ord3 is null) then 3
  else 4 
 end

Answer 2

order by isnull(ord1,1)+isnull(ord2,1)+isnull(ord3,1)

Answer 3

order by isnull(ord1,1)+isnull(ord2,1)+isnull(ord3,1) desc

Answer 4

order by (case when ord1 is null then 0 else 1 end)+
         (case when ord2 is null then 0 else 1 end)+
          (case when ord3 is null then 0 else 1 end) desc

Answer 5

1 test1 /Upload/AuthorizeImages/Authorize_633876795678648750.rar /Upload/LicenseImages/License_633861979492187500.jpg /Upload/PaperImages/License_633861979492343750.jpg
2 test2 /Upload/AuthorizeImages/Authorize_633870617524526250.jpg /Upload/LicenseImages/License_633870617524838750.jpg
3 test3 /Upload/AuthorizeImages/Authorize_633870617524526250.jpg /Upload/LicenseImages/License_633870617524838750.jpg /Upload/PaperImages/License_633870617525463750.jpg
4 test4
5 test5 /Upload/AuthorizeImages/Authorize_633875020253437500.jpg /Upload/LicenseImages/License_633877444174375000.jpg /Upload/PaperImages/License_633875047421250000.jpg
6 test6 /Upload/AuthorizeImages/Authorize_633875073470781250.jpg /Upload/LicenseImages/License_633875073470937500.jpg /Upload/PaperImages/License_633875073470937500.jpg
7 test7 /Upload/AuthorizeImages/Authorize_633882892892528750.doc /Upload/LicenseImages/License_633882892892841250.doc

Answer 6

CREATE TABLE TB(id INT,[name] VARCHAR(10),ord1 VARCHAR(100),ord2 VARCHAR(100),ord3 VARCHAR(100))INSERT dbo.TB
SELECT 1,'test1','/Upload/AuthorizeImages/Authorize_633876795678648750.rar','/Upload/LicenseImages/License_633861979492187500.jpg','/Upload/PaperImages/License_633861979492343750.jpg' UNION ALL
SELECT 2,'test2','/Upload/AuthorizeImages/Authorize_633870617524526250.jpg','/Upload/LicenseImages/License_633870617524838750.jpg', NULL UNION ALL          
SELECT 3,'test3','/Upload/AuthorizeImages/Authorize_633870617524526250.jpg','/Upload/LicenseImages/License_633870617524838750.jpg','/Upload/PaperImages/License_633870617525463750.jpg' UNION ALL    
SELECT 4,'test4',NULL,NULL,NULL UNION ALL                
SELECT 5,'test5','/Upload/AuthorizeImages/Authorize_633875020253437500.jpg','/Upload/LicenseImages/License_633877444174375000.jpg','/Upload/PaperImages/License_633875047421250000.jpg' UNION ALL    
SELECT 6,'test6','/Upload/AuthorizeImages/Authorize_633875073470781250.jpg','/Upload/LicenseImages/License_633875073470937500.jpg','/Upload/PaperImages/License_633875073470937500.jpg' UNION ALL    
SELECT 7,'test7','/Upload/AuthorizeImages/Authorize_633882892892528750.doc', '/Upload/LicenseImages/License_633882892892841250.doc',NULL SELECT * FROM dbo.TB ORDER BY LEN(ISNULL(ord1,'')+ISNULL(ord2,'')+ISNULL(ord3,'')) DESC--这个不太准确，参考
SELECT  id , [name] ,  ord1 ,  ord2 ,  ord3
FROM    ( SELECT    * ,( CASE WHEN ord1 IS NULL THEN 0 ELSE 1 END ) + ( CASE WHEN ord2 IS NULL THEN 0 ELSE 1 END ) + ( CASE WHEN ord3 IS NULL THEN 0  ELSE 1  END ) odr  FROM      dbo.TB   ) a
ORDER BY odr DESC
/*
id          name       ord1                                                                                                 ord2                                                                                                 ord3
----------- ---------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
1           test1      /Upload/AuthorizeImages/Authorize_633876795678648750.rar                                             /Upload/LicenseImages/License_633861979492187500.jpg                                                 /Upload/PaperImages/License_633861979492343750.jpg
3           test3      /Upload/AuthorizeImages/Authorize_633870617524526250.jpg                                             /Upload/LicenseImages/License_633870617524838750.jpg                                                 /Upload/PaperImages/License_633870617525463750.jpg
5           test5      /Upload/AuthorizeImages/Authorize_633875020253437500.jpg                                             /Upload/LicenseImages/License_633877444174375000.jpg                                                 /Upload/PaperImages/License_633875047421250000.jpg
6           test6      /Upload/AuthorizeImages/Authorize_633875073470781250.jpg                                             /Upload/LicenseImages/License_633875073470937500.jpg                                                 /Upload/PaperImages/License_633875073470937500.jpg
2           test2      /Upload/AuthorizeImages/Authorize_633870617524526250.jpg                                             /Upload/LicenseImages/License_633870617524838750.jpg                                                 NULL
7           test7      /Upload/AuthorizeImages/Authorize_633882892892528750.doc                                             NULL                                                                                                 NULL
4           test4      NULL                                                                                                 NULL                                                                                                 NULL(7 行受影响)
*/