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

[qiudong_5210]

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

[gogodiy]

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

[qiudong_5210]

效果不对啊……

[天下如山]

...楼上的只是思路 改下咯。

[天下如山]

楼上的只是思路，改下咯。

[qiudong_5210]

不知道如何下手啊 ……

[快溜]

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

[快溜]

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

[qiudong_5210]

[Quote=引用 7 楼 ssp2009 的回复:]

SQL code
order by isnull(ord1,1)+isnull(ord2,1)+isnull(ord3,1) desc
[/Quote]

效果还是不对 啊

[快溜]

给数据

[快溜]

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

[qiudong_5210]

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			

[qgqch2008]

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 行受影响)
*/