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
下面是一个可得到正确答案的写法,(为表增加一个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
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 会不会影响效率?
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 这样就行了,你再试试!
--SQL的BUG,无解:--关于此问题的测试--测试环境 declare @t1 table(id int) insert into @t1 select 1 union all select 2declare @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 行) --*/
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
你的查询不能直接执行改成如下:
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 会不会影响效率?
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 这样就行了,你再试试!
declare @t1 table(id int)
insert into @t1
select 1
union all select 2declare @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 行)
--*/
select 1 as CurMonth,GETDATE() as CurDay INTO TEST_DISPdelete 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')
这个算法在同一天有多个记录时,会有问题:谢谢上面两位,结贴!