22,206
社区成员
发帖
与我相关
我的任务
分享
select *, ROW_NUMBER() over(order by time) from table_1 where status = 0
union all
select *, ROW_NUMBER() over(order by time desc) from table_1 where status = 1
WITH a(d,i) AS (
SELECT '2016-2-1', 0 UNION
SELECT '2016-2-2 ',1 UNION
SELECT '2016-2-3',0 UNION
SELECT '2016-2-4',0 UNION
SELECT '2016-2-5',1
)
SELECT * FROM a ORDER BY i, CASE WHEN i=0 THEN a.d END, CASE WHEN a.i=1 THEN a.d END DESC
WITH a(d,i) AS (
SELECT '2016-2-1', 0 UNION
SELECT '2016-2-2 ',1 UNION
SELECT '2016-2-3',0 UNION
SELECT '2016-2-4',0 UNION
SELECT '2016-2-5',1
)
SELECT * FROM a ORDER BY i, DATEDIFF(d,a.d,'12/31/9999')*i