SELECT * FROM TB ORDER BY CASE WHEN 过期时间>=GETDATE() THEN 1 ELSE 0 END, 添加时间 ASC
理解错了 SELECT * FROM TB ORDER BY CASE WHEN 过期时间>=GETDATE() THEN 1 ELSE 2 END, 添加时间 ASC
SELECT * FROM TB ORDER BY CASE WHEN 过期时间>=GETDATE() THEN 1 ELSE 2 END, 添加时间 ASC
SELECT * FROM TB ORDER BY CASE WHEN 过期时间>=GETDATE() THEN 1 ELSE 0 END, 添加时间 ASC
ORDER BY CASE WHEN 过期时间>=convert(varchar(23),GETDATE(),120) THEN 2 ELSE 1 END
if(OBJECT_ID('tp') is not null) drop table tp;
create table tp(id int identity(1,1),添加时间 datetime, 过期时间 datetime);
insert into tp select '2008-1-1','2009-9-1' union all select '2008-1-1','2009-9-1' union all select '2008-3-1','2009-8-1' union all select '2008-4-1','2009-9-10' union all select '2008-6-1','2009-9-3' union all select '2009-9-1','2009-9-2' union all select '2009-3-1','2009-9-4' union all select '2009-4-1','2009-9-6' union all select '2009-5-1','2009-9-7' union all select '2009-5-2','2009-9-8' union all select '2009-3-2','2009-9-4' union all select '2009-1-1','2009-9-3 18:00:00' union all select '2009-10-1','2009-9-2 12:00:00' union all select '2009-4-12','2009-9-12' union all select '2009-3-12','2009-9-1'
select * from tp order by case when 过期时间>GETDATE() then 添加时间 else 过期时间 end
create table tb ( id int, addtime datetime, endtime datetime ) go insert into tb select 1,'2009-1-1','2009-4-1' union all select 2,'2009-2-1','2009-3-1' union all select 3,'2009-8-1','2009-10-1' union all select 4,'2009-9-1','2009-9-3' goselect * from tb order by case when endtime<=getdate() then 1 else 0 end, addtime asc goid addtime endtime ----------- ----------------------- ----------------------- 3 2009-08-01 00:00:00.000 2009-10-01 00:00:00.000 4 2009-09-01 00:00:00.000 2009-09-03 00:00:00.000 1 2009-01-01 00:00:00.000 2009-04-01 00:00:00.000 2 2009-02-01 00:00:00.000 2009-03-01 00:00:00.000 楼主是要1、未过期的数据按添加时间排序 2、再列出已过期的数据按过期日期排序 现在第二还没做到
ORDER BY
CASE WHEN 过期时间>=GETDATE() THEN 1 ELSE 0 END,
添加时间 ASC
SELECT * FROM TB
ORDER BY
CASE WHEN 过期时间>=GETDATE() THEN 1 ELSE 2 END,
添加时间 ASC
ORDER BY
CASE WHEN 过期时间>=GETDATE() THEN 1 ELSE 2 END,
添加时间 ASC
SELECT * FROM TB
ORDER BY
CASE WHEN 过期时间>=GETDATE() THEN 1 ELSE 0 END,
添加时间 ASC
CASE WHEN 过期时间>=convert(varchar(23),GETDATE(),120) THEN 2 ELSE 1 END
drop table tp;
create table tp(id int identity(1,1),添加时间 datetime, 过期时间 datetime);
insert into tp
select '2008-1-1','2009-9-1'
union all select '2008-1-1','2009-9-1'
union all select '2008-3-1','2009-8-1'
union all select '2008-4-1','2009-9-10'
union all select '2008-6-1','2009-9-3'
union all select '2009-9-1','2009-9-2'
union all select '2009-3-1','2009-9-4'
union all select '2009-4-1','2009-9-6'
union all select '2009-5-1','2009-9-7'
union all select '2009-5-2','2009-9-8'
union all select '2009-3-2','2009-9-4'
union all select '2009-1-1','2009-9-3 18:00:00'
union all select '2009-10-1','2009-9-2 12:00:00'
union all select '2009-4-12','2009-9-12'
union all select '2009-3-12','2009-9-1'
select * from tp
order by case when 过期时间>GETDATE() then 添加时间 else 过期时间 end
( id int,
addtime datetime,
endtime datetime
)
go
insert into tb
select 1,'2009-1-1','2009-4-1' union all
select 2,'2009-2-1','2009-3-1' union all
select 3,'2009-8-1','2009-10-1' union all
select 4,'2009-9-1','2009-9-3'
goselect * from tb
order by
case when endtime<=getdate() then 1 else 0 end,
addtime asc
goid addtime endtime
----------- ----------------------- -----------------------
3 2009-08-01 00:00:00.000 2009-10-01 00:00:00.000
4 2009-09-01 00:00:00.000 2009-09-03 00:00:00.000
1 2009-01-01 00:00:00.000 2009-04-01 00:00:00.000
2 2009-02-01 00:00:00.000 2009-03-01 00:00:00.000
楼主是要1、未过期的数据按添加时间排序
2、再列出已过期的数据按过期日期排序
现在第二还没做到