求如下查询语句:按传入查询时间得出一时段内的记录

acheng 2006-11-21 12:49:45
表一(内容是动态生成,记录个数不固定):

要查询的时间是2006-10-01
记录有可能的是A:
id time
1 2006-10-01 13:00:00
. 2006-10-01 17:10:00
. 2006-10-01 17:25:00
. 2006-10-01 22:30:00
5 2006-10-02 07:40:00
6 2006-10-02 08:00:00
7 2006-10-04 12:40:00
8 2006-10-04 21:00:00

记录有可能的是B:
id time
1 2006-10-01 13:00:00
. 2006-10-01 17:10:00
. 2006-10-01 17:25:00
. 2006-10-01 22:30:00
5 2006-10-02 04:40:00
6 2006-10-02 08:00:00
7 2006-10-02 12:00:00
8 2006-10-12 17:40:00
9 2006-10-12 20:00:00

记录有可能的是C:
id time
1 2006-10-01 13:00:00
. 2006-10-01 17:10:00
3 2006-10-01 17:35:00
4 2006-10-01 18:25:00
5 2006-10-01 23:30:00
6 2006-10-02 08:00:00
7 2006-10-02 12:00:00
8 2006-10-03 08:00:00
9 2006-10-03 12:00:00

要求根据查询参数:2006-10-01查询出:(2006-10-01最晚的两条记录,2006-10-2的最早一条记录)
A:结果
3 2006-10-01 17:25:00
4 2006-10-01 22:30:00
5 2006-10-02 07:40:00
B:结果
3 2006-10-01 17:25:00
4 2006-10-01 22:30:00
5 2006-10-02 04:40:00
C:结果
3 2006-10-01 17:35:00
4 2006-10-01 18:25:00
5 2006-10-01 23:30:00

...全文
161 3 打赏 收藏 举报
写回复
3 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
crazyflower 2006-11-21
select * from
(
select top 2 * from A where datediff(dd,date,'2006-10-01')=0 order by id desc
union all
select top 1 * from A where datediff(dd,date,'2006-10-01')=-1 order by id desc
)
order by id
  • 打赏
  • 举报
回复
acheng 2006-11-21
谢两位:

  • 打赏
  • 举报
回复
jackeyabc 2006-11-21
declare @t table( id int, dt datetime)
insert @t select 1 , '2006-10-01 13:00:00'
insert @t select 2 , '2006-10-01 17:10:00'
insert @t select 3 , '2006-10-01 17:25:00'
insert @t select 4 , '2006-10-01 22:30:00'
insert @t select 5 , '2006-10-02 07:40:00'
insert @t select 6 , '2006-10-02 08:00:00'
insert @t select 7 , '2006-10-04 12:40:00'
insert @t select 8 , '2006-10-04 21:00:00'


select * from (
select top 2 * from @t where
DATEDIFF(day, dt, '2006-10-1')='0'
and DATEDIFF(year, dt, '2006-10-1')='0'
and DATEDIFF(month, dt, '2006-10-1')='0'
order by dt desc) a
union all
select * from (

select top 1 * from @t where
DATEDIFF(month, dt, dateadd(day,1,'2006-10-1'))='0'
and DATEDIFF(day, dt, dateadd(day,1,'2006-10-1'))='0'
and DATEDIFF(year, dt, dateadd(day,1,'2006-10-1'))='0'
order by dt asc) b

  • 打赏
  • 举报
回复
相关推荐
发帖
应用实例

2.7w+

社区成员

MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
帖子事件
创建了帖子
2006-11-21 12:49
社区公告
暂无公告