一句简单的sql查询，在线求助
有四条记录
ID jobNo Date Month status
1 111 2007-2-10 2007-2-1 1
2 111 2007-2-20 2007-2-1 1
3 111 2007-3-1 2007-3-1 1
4 111 2007-3-10 2007-3-1 0
现在我需要的查询出的结果是第二条记录，即在status=0的月份的前一个月找出date最大的那条记录，如何写呢？
[解决办法]
select a.* from tb where date in( select max(date) from tb t1, (select dateadd(month , -1 , date) from tb where status = 0) t2 where convert(varchar(7),t1.date,120) = convert(varchar(7),t2.date,120))
[解决办法]
create table tb(ID int,jobNo int, Date datetime,[Month] datetime,status int)insert into tb values(1, 111, '2007-2-10', '2007-2-1', 1 )insert into tb values(2, 111, '2007-2-20', '2007-2-1', 1 )insert into tb values(3, 111, '2007-3-1' , '2007-3-1', 1 )insert into tb values(4, 111, '2007-3-10', '2007-3-1', 0 )goselect a.* from tb a where date in( select max(date) from tb t1, (select dateadd(month , -1 , date) tdate from tb where status = 0) t2 where convert(varchar(7),t1.date,120) = convert(varchar(7),t2.tdate,120))drop table tb/*ID jobNo Date Month status ----------- ----------- ------------------------------------------------------ ------------------------------------------------------ ----------- 2 111 2007-02-20 00:00:00.000 2007-02-01 00:00:00.000 1（所影响的行数为 1 行）*/
[解决办法]
create table #(ID int, jobNo int, [Date] varchar(10), Month varchar(10), status int)insert into #select 1, 111 , '2007-2-10' , '2007-2-1', 1 union allselect 2, 111 , '2007-2-20' , '2007-2-1', 1 union allselect 3, 111 , '2007-3-1' , '2007-3-1', 1 union allselect 4, 111 , '2007-3-10' , '2007-3-1', 0 select * from #where Date =(select max(Date) from # where Month=dateadd(month,-1,(select Month from # where status =0)) )/*ID jobNo Date Month status ----------- ----------- ---------- ---------- ----------- 2 111 2007-2-20 2007-2-1 1（所影响的行数为 1 行）*/
[解决办法]
create table tb(ID int,jobNo int, Date datetime,[Month] datetime,status int)insert into tb values(1, 111, '2007-2-10', '2007-2-1', 1 )insert into tb values(2, 111, '2007-2-20', '2007-2-1', 1 )insert into tb values(3, 111, '2007-3-1' , '2007-3-1', 1 )insert into tb values(4, 111, '2007-3-10', '2007-3-1', 0 )goselect * from tb a where exists(select 1 from tb where a.jobno =jobno and [date]< a.[date]) and id not in( select c.id from tb b left join tb c on datepart(mm,b.date) = datepart(mm,c.date) where b.status = 0 and a.jobno = b.jobno) drop table tb/*ID jobNo Date Month status ----------- ----------- ------------------------------------------------------ ------------------------------------------------------ ----------- 2 111 2007-02-20 00:00:00.000 2007-02-01 00:00:00.000 1（所影响的行数为 1 行）*/
