sql多字段排序问题

[tian31598805]

数据库现有排序字段

图片字段 日期字段
photo, date
NULL 2011-5-9 10:30:34
2022.JPG 2011-5-9 10:44:34
NULL 2011-5-8 10:44:34
2055.JPG 2011-5-9 10:50:34


现想得出结果如下所示:

photo, date

2055.JPG 2011-5-9 10:50:34
2022.JPG 2011-5-9 10:44:34
NULL 2011-5-9 10:30:34
NULL 2011-5-8 10:44:34

这种排序应该在sql里应该如何实现。谢谢!

[kingtiy]

select * from [tb] order by photo desc,date desc

[AcHerat]

select *
from tb
order by isnull(photo,0) desc,date desc

[tian31598805]

[Quote=引用 1 楼 acherat 的回复:]

select *
from tb
order by isnull(photo,0) desc,date desc

[/Quote]

这个(photo,0)是什么意思.查询出的结果是相反的

[AcHerat]

select *
from tb
order by photo desc,date desc

--这样试试！ 看差了，还以为photo都是数字了。 

[叶子]

declare @t table (photo varchar(8),date datetime)
insert into @t
select null,'2011-5-9 10:30:34' union all
select '2022.JPG','2011-5-9 10:44:34' union all
select null,'2011-5-8 10:44:34' union all
select '2055.JPG','2011-5-9 10:50:34'

select * from @t 
order by photo desc,date desc
/*
photo    date
-------- -----------------------
2055.JPG 2011-05-09 10:50:34.000
2022.JPG 2011-05-09 10:44:34.000
NULL     2011-05-09 10:30:34.000
NULL     2011-05-08 10:44:34.000
*/

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2011-05-09 11:31:41
-- Verstion:
--      Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
--	Jul  9 2008 14:43:34 
--	Copyright (c) 1988-2008 Microsoft Corporation
--	Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([photo] varchar(8),[date] datetime)
insert [tb]
select null,'2011-5-9 10:30:34' union all
select '2022.JPG','2011-5-9 10:44:34' union all
select null,'2011-5-8 10:44:34' union all
select '2055.JPG','2011-5-9 10:50:34'
--------------开始查询--------------------------
select * from [tb] order by photo desc,date desc
----------------结果----------------------------
/* photo    date
-------- -----------------------
2055.JPG 2011-05-09 10:50:34.000
2022.JPG 2011-05-09 10:44:34.000
NULL     2011-05-09 10:30:34.000
NULL     2011-05-08 10:44:34.000

(4 行受影响)

*/