数据:
代号 日期 状态
001 2009-01-08 1
002 2009-01-11 1
003 2009-01-12 1
001 2009-01-20 0
004 2009-02-01 1在2009-01-01至2009-01-31时间段内查询得到:
代号 日期 状态
002 2009-01-11 1
003 2009-01-12 1
代号 日期 状态
001 2009-01-08 1
002 2009-01-11 1
003 2009-01-12 1
001 2009-01-20 0
004 2009-02-01 1在2009-01-01至2009-01-31时间段内查询得到:
代号 日期 状态
002 2009-01-11 1
003 2009-01-12 1
where 状态=1 and 日期 between '2009-01-01' and '2009-01-31'
order by 日期没看出来什么规则
select 代号,状态,max(日期) from TB
where 状态=1 and 日期 between '2009-01-01' and '2009-01-31'
group by 代号,状态
谢谢你!
你的方法可能不行,001记录会出现。
create table #TA
(
id varchar(10),
date varchar(20),
Status varchar(10)
)
insert into #TA
select '001','2009-01-08','1' union all
select '002','2009-01-11','1' union all
select '003','2009-01-12','1' union all
select '001','2009-01-20','0' union all
select '004','2009-02-01','1'select a.id,a.date,b.status from
(select id,max(date) as date from #TA
group by id) a,#TA b
where a.id=b.id and a.date=b.date and status='1' and b.date between '2009-01-01' and '2009-01-31'id date status
002 2009-01-11 1
003 2009-01-12 1