create table a (col1 smalldatetime,col2 smalldatetime, col3 char(10)) go
select col1 from a order by col1 union select col2 from a order by col2
两个order by 通不过,
顺带还有一个问题:
select top1 col1 from a union select top1 col1 from a order by col1 asc --和 select top1 col1 from a union select top1 col1 from a order by col1 desc
这两个结果好象都是一样的 为什么呢?但 asc和 desc的结果集是不一样的得。这是怎么回事呢?
------解决方案-------------------- asc desc 本身就不一样 既然按照这个排序 那又怎么会一样呢
------解决方案-------------------- union子查询不能加order 可以外套个select select col1 from( select col1 from a order by col1 ) t1 union select col2 from( select col2 from a order by col2 ) t2
------解决方案--------------------
------解决方案--------------------
SQL code
select * from (select top 100 percent col1 from a order by col1)a
union all
select * from (select top 100 percent col2 from a order by col2)b
------解决方案--------------------
SQL code
小技巧:这时候如果你想对2输入表分别进行排序,比如上面的#a的a列升序,#b的d列降序,可以这样
select a,b
from (
select *,flag=1 from #a
union all
select *,2 from #b
) l
order by case when flag=1 then a end ,case when flag=2 then b end desc
/*
a b
----------- -----------
5 9
4 9
4 2
3 2
1 2
3 9
4 8
*/