34,576
社区成员
发帖
与我相关
我的任务
分享
create table #tb([发布日期] datetime,[有效日期] datetime,[状态] varchar(10))
insert #tb select '2009-4-1','2009-10-1','有效'
insert #tb select '2009-3-1','2009-3-5','无效'
insert #tb select '2009-2-28','2009-5-1','有效'
insert #tb select '2009-2-20','2009-10-1','有效'
insert #tb select '2009-2-18','2009-2-20','无效'
select * from #tb order by 状态 desc,发布日期 desc
发布日期 有效日期 状态
----------------------- ----------------------- ----------
2009-04-01 00:00:00.000 2009-10-01 00:00:00.000 有效
2009-02-28 00:00:00.000 2009-05-01 00:00:00.000 有效
2009-02-20 00:00:00.000 2009-10-01 00:00:00.000 有效
2009-03-01 00:00:00.000 2009-03-05 00:00:00.000 无效
2009-02-18 00:00:00.000 2009-02-20 00:00:00.000 无效
(5 行受影响)
select * from tb
order by 状态 desc,发布日期 desc
if OBJECT_ID('tb') is not null
Drop table tb
create table tb([发布日期] datetime,[有效日期] datetime,[状态] varchar(10))
insert tb select '2009-4-1','2009-10-1','有效'
insert tb select '2009-3-1','2009-3-5','无效'
insert tb select '2009-2-28','2009-5-1','有效'
insert tb select '2009-2-20','2009-10-1','有效'
insert tb select '2009-2-18','2009-2-20','无效'
go
select * from tb order by case when 状态 ='有效' then 0 else 1 end,发布日期 desc
/*
发布日期 有效日期 状态
----------------------- ----------------------- ----------
2009-04-01 00:00:00.000 2009-10-01 00:00:00.000 有效
2009-02-28 00:00:00.000 2009-05-01 00:00:00.000 有效
2009-02-20 00:00:00.000 2009-10-01 00:00:00.000 有效
2009-03-01 00:00:00.000 2009-03-05 00:00:00.000 无效
2009-02-18 00:00:00.000 2009-02-20 00:00:00.000 无效
(5 行受影响)
*/