UNION 与 ORDER BY 结合出错的
测试数据如下:
/*
select 1 as CurMonth,GETDATE() as CurDay INTO TEST_DISP
delete TEST_DISP
insert TEST_DISP values(11,'2003-11-01')
insert TEST_DISP values(12,'2003-12-01')
insert TEST_DISP values(1,'2004-1-01')
insert TEST_DISP values(2,'2004-2-01')
insert TEST_DISP values(3,'2004-3-01')
insert TEST_DISP values(4,'2004-4-01')
insert TEST_DISP values(5,'2004-5-01')
Select * from TEST_DISP
*/
我的查询是从2003年取最后的一天和2004 年的记录按CurDay顺序输出
语句如下
select s.* from
(SELECT top 1 CurMonth,CurDay FROM TEST_DISP where CurDay< '2004-01-01' order by CurDay desc
UNION
SELECT CurMonth,CurDay FROM TEST_DISP where CurDay>= '2004-01-01' ) s order by CurDay
但发现2003的数据总是取在 2003-11-1那一条记录,实际上order by CurDay desc并未起作用,去掉或改成order by CurDay ,仍然如此,问题出在哪里?