有四条记录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最大的那条记录,如何写呢?
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 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)
)
from table a
where not exists(select 1 from table where status = 0)
and not exists(select 1 from table where a.jobno =jobno and [date]>a.[data])
select * from t
where Date =
(select max(Date) from t where Month=datediff(month,1,(select Month from t where status =0))
)
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 行)
*/
from table a
where not exists(select 1 from table b left join table c on convert(varchar(7),b.date,120) = convert(varchar(7),c.date,120) where b.status = 0)
and not exists(select 1 from table where a.jobno =jobno and [date]> a.[data])
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 all
select 2, 111 , '2007-2-20' , '2007-2-1', 1 union all
select 3, 111 , '2007-3-1' , '2007-3-1', 1 union all
select 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 行)
*/
(
Id int,
jobNo varchar(10),
pDate datetime,
pMonth datetime,
status bit
)insert into tab
select 1,'111','2007-2-10','2007-2-1',1 union all
select 2,'111','2007-2-20','2007-2-1',1 union all
select 3,'111','2007-3-1','2007-3-1',1 union all
select 4,'111','2007-3-10','2007-3-1',0select * from tab where pdate=
(select max(pdate) from tab where
pMonth=(select dateadd(month,-1,pMonth) from tab where status=0))
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 行)
*/
select *
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 ) 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 行)
*/
declare @tb table (id int identity(1,1),jobno int,date datetime,month datetime,status int)
insert into @tb select 111,'2007-2-10','2007-2-1',1
insert into @tb select 111,'2007-2-20','2007-2-1',1
insert into @tb select 111,'2007-3-1','2007-3-1',1
insert into @tb select 111,'2007-3-10','2007-3-1',0
select * from @tb where id=
(select top 1 id from @tb where month(dateadd(month,1,date))=
(select month(date) from @tb where status=0) order by jobno desc)2 111 2007-02-20 00:00:00.000 2007-02-01 00:00:00.000 1