一个查询语句或函数

求助 一个查询语句或函数
device_id device_status device_address now_date
00001 故障 A区 2012-03-01
00001 故障 A区 2012-03-02
00001 故障 A区 2012-03-03
00002 故障 A区 2012-03-01
00002 故障 A区 2012-03-02
00002 无故障 A区 2012-03-03
00003 故障 B区 2012-03-02
00003 故障 B区 2012-03-03
00003 故障 B区 2012-03-04
00004 故障 B区 2012-03-02
00004 无故障 B区 2012-03-03
00004 故障 B区 2012-03-04
------------------------
返回一个 以小区 和最后一次 日期 统计的表
Fault_Count device_address last_date
1 A区 2012-03-03
2 B区 2012-03-04
------------------------
表值函数 或查询语句都可以

[解决办法]

SQL code

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