declare @a table(id int ,[name] varchar(20)) insert @a select 1,'a' union all select 2,'b' union all select 3,'c' union all select 4,'d' union all select 5,'e' union all select 6,'f' union all select 7,'h' union all select 8,'g'select * from (select top 8 * from @a order by id desc)a union all select * from (select top 8 * from @a order by id asc)b (所影响的行数为 8 行)id name ----------- -------------------- 8 g 7 h 6 f 5 e 4 d 3 c 2 b 1 a 1 a 2 b 3 c 4 d 5 e 6 f 7 h 8 g(所影响的行数为 16 行)
declare @a table(id int ,[name] varchar(20)) insert @a select 1,'a' union all select 2,'b' union all select 3,'c' union all select 4,'d' union all select 5,'e' union all select 6,'f' union all select 7,'h' union all select 8,'g'select * from (select * from @a a union all select * from @a b)c order by id,[name] desc(所影响的行数为 8 行)id name ----------- -------------------- 1 a 1 a 2 b 2 b 3 c 3 c 4 d 4 d 5 e 5 e 6 f 6 f 7 h 7 h 8 g 8 g(所影响的行数为 16 行)
select *from a
order by 列名 union all
select *from b
order by 列名
数据量大用union all这样查询快
select *from a
order by 列名desc
union all
select *from b
order by 列名 desc
数据量大用union all这样查询快
先定义表排序方式,再用union all连接
insert @a
select 1,'a' union all
select 2,'b' union all
select 3,'c' union all
select 4,'d' union all
select 5,'e' union all
select 6,'f' union all
select 7,'h' union all
select 8,'g'select * from (select top 8 * from @a order by id desc)a
union all
select * from (select top 8 * from @a order by id asc)b (所影响的行数为 8 行)id name
----------- --------------------
8 g
7 h
6 f
5 e
4 d
3 c
2 b
1 a
1 a
2 b
3 c
4 d
5 e
6 f
7 h
8 g(所影响的行数为 16 行)
insert @a
select 1,'a' union all
select 2,'b' union all
select 3,'c' union all
select 4,'d' union all
select 5,'e' union all
select 6,'f' union all
select 7,'h' union all
select 8,'g'select *
from (select * from @a a
union all
select * from @a b)c order by id,[name] desc(所影响的行数为 8 行)id name
----------- --------------------
1 a
1 a
2 b
2 b
3 c
3 c
4 d
4 d
5 e
5 e
6 f
6 f
7 h
7 h
8 g
8 g(所影响的行数为 16 行)