假设有这么一个表A:
id time month year day content
1 2006-1-1 1 2006 1 xxxxx
2 2006-1-2 1 2006 2 xxxxx
3 2006-2-1 2 2006 1 xxxxx
4 2006-2-12 2 2006 12 xxxxx
5 2006-7-21 7 2006 21 xxxxx
——————————————————————(记录的日期如月份年份,都不一定是连续的)
问题是,我该如何写查询语句才能得到2006年2月份前后发表的记录各2条呢?
需要的结果:
id time month year day content
2 2006-1-2 1 2006 2 xxxxx
5 2006-7-21 7 2006 21 xxxxx
id time month year day content
1 2006-1-1 1 2006 1 xxxxx
2 2006-1-2 1 2006 2 xxxxx
3 2006-2-1 2 2006 1 xxxxx
4 2006-2-12 2 2006 12 xxxxx
5 2006-7-21 7 2006 21 xxxxx
——————————————————————(记录的日期如月份年份,都不一定是连续的)
问题是,我该如何写查询语句才能得到2006年2月份前后发表的记录各2条呢?
需要的结果:
id time month year day content
2 2006-1-2 1 2006 2 xxxxx
5 2006-7-21 7 2006 21 xxxxx
insert @a select 1 ,'2006-1-1', 1 ,2006, 1 ,'xxxxx'
union all select 2 ,'2006-1-2', 1 ,2006, 2 ,'xxxxx'
union all select 3 ,'2006-2-1', 2 ,2006, 1 ,'xxxxx'
union all select 4 ,'2006-2-12', 2, 2006, 12 ,'xxxxx'
union all select 5 ,'2006-7-21', 7 ,2006, 21 ,'xxxxx'select top 1 * from @a where time=all(select max(time) from @a a where [year]=2006 and [month]<2)
union all
select top 1 * from @a where time=all(select min(time) from @a a where [year]=2006 and [month]>2)
declare @a table(id int, time smalldatetime, [month] int, [year] int, [day] int, content varchar(10))
insert @a select 1 ,'2006-1-1', 1 ,2006, 1 ,'xxxxx'
union all select 2 ,'2006-1-2', 1 ,2006, 2 ,'xxxxx'
union all select 3 ,'2006-2-1', 2 ,2006, 1 ,'xxxxx'
union all select 4 ,'2006-2-12', 2, 2006, 12 ,'xxxxx'
union all select 5 ,'2006-7-21', 7 ,2006, 21 ,'xxxxx'
select top 1 * from @a where convert(char(7),[time],21)<'2006-02' order by [time] desc
select top 1 * from @a where convert(char(7),[time],21)>'2006-02' order by [time]
insert test select 1,'2006-1-1',1,2006,1,'xxxxx'
union all select 2,'2006-1-2',1,2006,2,'xxxxx'
union all select 3,'2006-2-1',2,2006,1,'xxxxx'
union all select 4,'2006-2-12',2,2006,12,'xxxxx'
union all select 5,'2006-7-21',7,2006,21,'xxxxx'select * from test where id in
(
select id-1 from test where day=(select min(day) from test where month=2 group by month)
union all
select id+1 from test where day=(select max(day) from test where month=2 group by month)
)/*
2 2006-01-02 00:00:00.000 1 2006 2 xxxxx
5 2006-07-21 00:00:00.000 7 2006 21 xxxxx
*/
(
id int,
time datetime,
month int,
year int ,
day int,
content varchar(10)
)insert into #date
select 1, '2006-1-1' , 1 , 2006 , 1 , 'xxxxx' union all select
2 , '2006-1-2' , 1 , 2006 , 2, ' xxxxx' union all select
3, ' 2006-2-1' , 2 , 2006 , 1 , 'xxxxx' union all select
4, '2006-2-12' , 2 , 2006 , 12 , 'xxxxx' union all select
5, '2006-7-21' , 7 , 2006, 21 , 'xxxxx'
select * from #date where time =(select max(time) from #date where datepart(mm,time) < 2 and datepart(yy,time) =2006)
union all
select * from #date where time =(select min(time) from #date where datepart(mm,time) > 2 and datepart(yy,time) =2006)
drop table #date
2 2006-1-2 1 2006 2 xxxxx
5 2006-7-21 7 2006 21 xxxxx
select id , convert(varchar(10),time,120) as time, month , year, day, content from #date where time =(select max(time) from #date where datepart(mm,time) < 2 and datepart(yy,time) =2006)
union all
select id , convert(varchar(10),time,120)as time , month , year , day , content from #date where time =(select min(time) from #date where datepart(mm,time) > 2 and datepart(yy,time) =2006)
create table #date
(
id int,
time datetime,
month int,
year int ,
day int,
content varchar(10)
)insert into #date
select 1, '2005-1-1' , 1 , 2006 , 1 , 'xxxxx' union all select
2 , '2005-6-2' , 1 , 2006 , 2, ' xxxxx' union all select
3, ' 2006-2-1' , 2 , 2006 , 1 , 'xxxxx' union all select
4, '2006-2-12' , 2 , 2006 , 12 , 'xxxxx' union all select
6, '2007-2-12' , 2 , 2007 , 12 , 'xxxxx' union all select
5, '2007-7-21' , 7 , 2007, 21 , 'xxxxx' select * from #date where time =(select max(time) from #date where convert(varchar(6),time,112)< '200602' )
union all
select * from #date where time =(select min(time) from #date where convert(varchar(6),time,112) > '200602' )
drop table #date
6 2007-02-12 00:00:00.000 2 2007 12 xxxxx