比如 我表中有 创建的时间CreateDate,和ReplyDate回复时间。现在我要排序,sql语句为:select * from table1 order by ReplyDate desc,CreateDate desc,id desc.得到的结果是:id CreateDate ReplyDate
4 2010-11-01 14:20 2010-11-17 10:10
2 2010-11-11 13:20 2010-11-17 08:10
1 2010-10-17 08:01 2010-11-01 08:11
8 2010-11-17 08:20 null
5 2010-11-16 14:20 null 我想要的结果是 CreateDate 时间是今天发的,而ReplyDate为空的话,也是排在最前面。之后再按ReplyDate,最早的回去时间排。
也就是这样的
id CreateDate ReplyDate
4 2010-11-01 14:20 2010-11-17 10:10
8 2010-11-17 08:20 null
2 2010-11-11 13:20 2010-11-17 08:10
5 2010-11-16 14:20 null
1 2010-09-17 08:01 2010-11-01 08:11
该怎么写这样的SQl语句类,求助各位大侠
4 2010-11-01 14:20 2010-11-17 10:10
2 2010-11-11 13:20 2010-11-17 08:10
1 2010-10-17 08:01 2010-11-01 08:11
8 2010-11-17 08:20 null
5 2010-11-16 14:20 null 我想要的结果是 CreateDate 时间是今天发的,而ReplyDate为空的话,也是排在最前面。之后再按ReplyDate,最早的回去时间排。
也就是这样的
id CreateDate ReplyDate
4 2010-11-01 14:20 2010-11-17 10:10
8 2010-11-17 08:20 null
2 2010-11-11 13:20 2010-11-17 08:10
5 2010-11-16 14:20 null
1 2010-09-17 08:01 2010-11-01 08:11
该怎么写这样的SQl语句类,求助各位大侠
--> 数据库版本:
--> Microsoft SQL Server 2008 (RTM) - 10.0.1600.22
--> 测试数据:[TB]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[TB]')
AND type in (N'U'))
DROP TABLE [TB]
GO---->建表
create table [TB]([id] int,[CreateDate] datetime,[ReplyDate] datetime)
insert [TB]
select 4,'2010-11-01 14:20','2010-11-17 10:10' union all
select 2,'2010-11-11 13:20','2010-11-17 08:10' union all
select 1,'2010-10-17 08:01','2010-11-01 08:11' union all
select 8,'2010-11-17 08:20',null union all
select 5,'2010-11-16 14:20',null
GO--> 查询结果
SELECT * FROM [TB] order by case when ReplyDate is null then CreateDate else ReplyDate end desc,
CreateDate desc
--> 删除表格
--DROP TABLE [TB]
CreateDate desc