实现子查询排序

USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL
DROP TABLE t
GO
CREATE TABLE t(id INT,[time] DATETIME,num1 INT)
INSERT INTO t
SELECT 9,'2018-02-09 15:55:40',3
UNION ALL SELECT 8,'2018-02-08 15:55:26',1
UNION ALL SELECT 1,'2018-02-01 15:20:41',1
UNION ALL SELECT 2,'2018-02-02 15:20:52',1
UNION ALL SELECT 3,'2018-02-03 15:20:57',3

UNION ALL SELECT 4,'2018-02-04 15:20:59',2
UNION ALL SELECT 5,'2018-02-05 15:21:04',3
UNION ALL SELECT 6,'2018-02-06 15:21:09',3
UNION ALL SELECT 7,'2018-02-07 15:21:14',1

--实现子查询预排序
; WITH t1 AS (
	SELECT top 99.999999 percent * FROM t WHERE num1=1 ORDER BY [time] DESC
),
t2 AS
( 
	SELECT top 99.999999 percent * FROM t WHERE num1=3 ORDER BY [time] ASC
)
SELECT * FROM t1
UNION ALL
SELECT * FROM t2
/*
id	time	                num1
8	2018-02-08 15:55:26.000	1
7	2018-02-07 15:21:14.000	1
2	2018-02-02 15:20:52.000	1
1	2018-02-01 15:20:41.000	1
3	2018-02-03 15:20:57.000	3
5	2018-02-05 15:21:04.000	3
6	2018-02-06 15:21:09.000	3
9	2018-02-09 15:55:40.000	3
*/