create table test(device_id varchar(10),device_status varchar(10),device_address varchar(10),now_date date)

insert into test(device_id ,device_status, device_address, now_date)
 select '00001','故障','A区',' 2012-03-01'
 union all select '00001','故障','A区','2012-03-02'
 union all select '00001','故障','A区','2012-03-03'
  union all select '00002','故障','A区','2012-03-01'
 union all select '00002','故障','A区','2012-03-02'
 union all select '00002','无故障','A区','2012-03-03'
 union all select '00003','故障','B区','2012-03-02'
 union all select '00003','故障','B区','2012-03-03'
 union all select '00003','故障','B区','2012-03-04'
 union all select '00004','故障','B区','2012-03-02'
 union all select '00004','无故障','B区','2012-03-03'
 union all select '00004','故障','B区','2012-03-04'




 Select count(*)as Fault_Count,device_address,max(now_date) as last_date
 from test where device_status='故障' group by device_address

------解决方案--------------------

SQL code

--1.创建临时表
create table #TB(device_id varchar(10),device_status varchar(10),device_address varchar(10),now_date datetime)
insert into #TB values('00001', '故障'  , 'A区', '2012-03-01')
insert into #TB values('00001', '故障'  , 'A区', '2012-03-02')
insert into #TB values('00001', '故障'  , 'A区', '2012-03-03')
insert into #TB values('00002', '故障'  , 'A区', '2012-03-01')
insert into #TB values('00002', '故障'  , 'A区', '2012-03-02')
insert into #TB values('00002', '无故障', 'A区', '2012-03-03')
insert into #TB values('00003', '故障'  , 'B区', '2012-03-02')
insert into #TB values('00003', '故障'  , 'B区', '2012-03-03')
insert into #TB values('00003', '故障'  , 'B区', '2012-03-04')
insert into #TB values('00004', '故障'  , 'B区', '2012-03-04')
insert into #TB values('00004', '无故障', 'B区', '2012-03-04')
insert into #TB values('00004', '故障'  , 'B区', '2012-03-04')
go


--2.查询

with a as (
    select count(device_status) as Fail_counts,device_address,now_date from #TB where device_status='故障' group by device_address,now_date
)
,b as (
select device_address,last_date=max(now_date) from #TB where device_status='故障' group by device_address,device_status
)

select a.Fail_counts,a.device_address,now_date as last_date from a 
join b on a.now_date=b.last_date and a.device_address=b.device_address



--3.删除临时表 
truncate table #TB 
drop table #TB