sql查询请教～
假设有这么一个表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
[解决办法]
把前一筆和後一筆分別取出,放在一個零時表裡
然後 從這個零時表裡抓取
[解决办法]
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 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)
[解决办法]
select * from table_name where convert(char(7),time,21) < '2006-02 '
[解决办法]
先算出前后两个月是几月, 然后分别用> = 和 <=做条件,检索MAX 和MIN
因为不知道是什么库, 所以没写SQL,只能给个思路
[解决办法]
-- 刚刚看错了--
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]
[解决办法]
create table test(id int,time datetime,month int,year int,day int ,content varchar(20))
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)
)
/*
22006-01-02 00:00:00.000120062xxxxx
52006-07-21 00:00:00.0007200621xxxxx
*/
[解决办法]
create table #date
(
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
[解决办法]
id time month year day content
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
