UNION 与ORDER BY 结合出错

[bluedrumbeat]

测试数据如下
/*
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年的记录按时间顺序输出:
我的做法如下
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-01那条,也就是order by CurDay desc没起作用,
实际不用排序或用order by CurDay 结果是一样,总是11月那条,为什么为这样?
如何实现这个查询? 前提是不需要中间表

[shuichangliu]

select a.* from(SELECT top 1 CurMonth,CurDay
FROM TEST_DISP where CurDay< '2004-01-01'
order by CurDay desc) a
Union all
select b.* from
(SELECT CurMonth,CurDay FROM TEST_DISP where CurDay>= '2004-01-01' order by CurDay ) b

[bluedrumbeat]

下面是一个可得到正确答案的写法,(为表增加一个Key) 这样写太别扭了
select * from TEST_DISP
where key = (SELECT top 1 key FROM TEST_DISP where CurDay< '2004-01-01' order by CurDay desc)
OR key in
(SELECT key FROM TEST_DISP where CurDay>= '2004-01-01')
ORDER BY CurDay

[bluedrumbeat]

To shuichangliu :
你的查询不能直接执行

改成如下:
select a.* from(SELECT top 1 CurMonth,CurDay
FROM TEST_DISP where CurDay< '2004-01-01'
order by CurDay desc) a
Union all
select b.* from
(SELECT TOP 100 PERCENT CurMonth,CurDay FROM TEST_DISP where CurDay>= '2004-01-01' order by CurDay order by CurDay) b

这样用TOP 100 PRECENT 会不会影响效率?

[SunnyJing0201]

select s.* from
(SELECT CurMonth,CurDay FROM TEST_DISP
where CurDay in( SELECT max(CurDay) FROM TEST_DISP where CurDay< '2004-01-01')
UNION
SELECT CurMonth,CurDay FROM TEST_DISP where CurDay>= '2004-01-01' ) s order by CurDay

这样就行了，你再试试！

[zjcxc]

--SQL的BUG,无解:

--关于此问题的测试

--测试环境
declare @t1 table(id int)
insert into @t1
select 1
union all select 2

declare @t2 table(id int)
insert into @t2
select 1
union all select 3

--查询
select top 1 * from(
select top 1 id from @t1 order by id desc
union all
select top 1 id from @t2 order by id desc
)a order by id desc

/*--测试结果,显然不正确
id
-----------
1

（所影响的行数为 1 行）
--*/

[SunnyJing0201]

不能用TOP 1 的，如果是下边这样，不就丢失数据了吗？
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(13,'2003-12-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')

[shuichangliu]

TOP 100 PRECENT

我用20万条记录测试了一下，查看了执行分计划 估计不会影响效率

[bluedrumbeat]

To SunnyJing0201
这个算法在同一天有多个记录时,会有问题:

谢谢上面两位,结贴!