求助 一个查询语句或函数

Question

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
------------------------------------------------------------
表值函数 或查询语句都可以

Answer 1

select count(device_id),device_address ,max(now_date) as last_date
from T
where device_status = '故障'
group by device_address 

Answer 2

select device_address,max(now_date) now_date
from tb
group by device_address

Answer 3

select count(device_id) Fault_Count,device_address,max(now_date) as last_date
from T
where device_status = '故障'
group by device_address  

Answer 4

select t.* from tb t where now_date = (select max(now_date) from tb where device_address = t.device_address) order by t.device_addressselect t.* from tb t where not exists (select 1 from tb where device_address = t.device_address and  now_date  > t.now_date ) order by t.device_address

Answer 5

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-02')
insert into tb values('00004', '无故障', 'B区', '2012-03-03')
insert into tb values('00004', '故障'  , 'B区', '2012-03-04')
goselect t.* from tb t where device_status = '故障' and not exists (select 1 from tb where device_status = '故障' and device_address = t.device_address and (now_date  > t.now_date or (now_date = t.now_date and device_id > t.device_id)) ) order by t.device_address
/*
device_id  device_status device_address now_date                                               
---------- ------------- -------------- ------------------------------------------------------ 
00001      故障            A区             2012-03-03 00:00:00.000
00004      故障            B区             2012-03-04 00:00:00.000（所影响的行数为 2 行）
*/
select (select count(1) from 
(
  select device_address , max(now_date) now_date from tb where device_status = '故障' group by device_address
) n where now_date < m.now_date) + 1 Fault_Count , m.* from
(
  select device_address , max(now_date) now_date from tb where device_status = '故障' group by device_address
) m
/*
Fault_Count device_address now_date                                               
----------- -------------- ------------------------------------------------------ 
1           A区             2012-03-03 00:00:00.000
2           B区             2012-03-04 00:00:00.000（所影响的行数为 2 行）*/drop table tb

Answer 6

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

Answer 7

dawugui：
  查询语句是对的 但是速度很慢啊 我这张表里 现在只有 7w多条记录，跑这个查询语句用了 两秒多，有没有更好的办法？

Answer 8

7w多条记录,两秒多,还嫌弃慢?如果是sql 2005,可改为如下:
select row_number() over(order by now_date) Fault_Count , m.* from
(
select device_address , max(now_date) now_date from tb where device_status = '故障' group by device_address
) m
order by Fault_Count 

Answer 9

--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 这个。

Answer 10

--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 
这个。第一列不是编号 而是故障数量