求一SQL语句

[zlydaaa]

刚才在这儿看到这么一个问题

px表中有两个字段 id 和 datetime, 如果datetime内时间是3天内的则排序在最前，并按id号排序,3天后的按id 排序。

回答是
Select * from px
order by (case when datediff(d,datatime,getdate())<=3 then 1 else 2 end),id

但现在
如果datetime内时间是3天内的则排序在最前,并按datetime排序,3天后的按照ID排序.
该怎么写?

[leimin]

Select * from px where datediff(d,datetime,getdate()) <= 3 order by datetime
Union all
Select * from px where datediff(d,datetime,getdate()) > 3 order by id

[lyyrw]

使用临时表

[lyyrw]

SELECT IDENTITY(int, 1,1) as id0, Recode_ID, [Date] into #temp
FROM FA_VIEW_10100000
where datediff(d,date,getdate())<=90 order by date desc
go
insert into #temp
select Recode_ID, [Date]
FROM FA_VIEW_10100000
where datediff(d,date,getdate())>90 order by recode_id
go
select recode_id,date from #temp order by id0
drop table #temp
go

[txlicenhe]

Sorry: Union all 不支持前后语句用order by
try:
Select id,[datetime] from
(
Select *,(select sum(1) from px where [datetime] <= a.[datetime]) as iid1,1 as iid2 from px a where datediff(day,datetime,getdate()) <= 3
Union all
Select *,id as iid1,2 as iid2 from px where datediff(day,datetime,getdate()) > 3
) temp
order by iid2,iid1

[Robin]

用临时表，的了！

[Robin]

不对，确切的说，用view就可以了！

[txlicenhe]

用这个一定行了。
Select id,[datetime] from
(
Select *,(select sum(1) from px where [datetime] <= a.[datetime]) as iid1,1 as iid2 from px a where datediff(day,datetime,getdate()) <= 3
Union all
Select *,id as iid1,2 as iid2 from px where datediff(day,datetime,getdate()) > 3
) temp
order by iid2,iid1

[CCEO]

Select * from px
order by
case when datediff(d,[datatime],getdate())<=3 then [datatime]
else '9999-12-31 23:59:59'
end,
CASE WHEN datediff(d,[datatime],getdate())<=3 then -2147483648
ELSE ID
END


呵呵

[zjcxc]

如果你的日期排序仅按日期大小,不考虑时间的话,可以用下面的方法:

select * from px
order by case when datediff(day,[datetime],getdate())<=3 then datediff(day,[datetime],getdate()) else myid+3 end

[zjcxc]

上面的是我测试的语句,按照你的表应该是:

select * from px
order by case when datediff(day,[datetime],getdate())<=3 then datediff(day,[datetime],getdate()) else id+3 end

[txlicenhe]

Try:
Select * from px where datediff(d,datetime,getdate()) <= 3 order by datetime
Union all
Select * from px where datediff(d,datetime,getdate()) > 3 order by id

[zjcxc]

Select * from px where datediff(d,datetime,getdate()) <= 3 order by datetime
Union all
Select * from px where datediff(d,datetime,getdate()) > 3 order by id