oracle 分组+去重 sql请教

[wasp520]

求sql语句

现有表
create table t1{
id varcher2(20) primary key,
pc varcher2(20),
tname varcher2(20),
tver varcher2(20),
tstatus varchar2(20),
time1 date,
time2 date
}

实例t1数据
insert into t1 select '001','p1','tn1','tv1','on',sysdate,'2011-09-23 10:22:33' from dual;
insert into t1 select '002','p1','tn1','tv1','oooo',sysdate,'2011-09-21 10:22:33' from dual;
insert into t1 select '003','p1','tn1','tv1','off',sysdate,'2011-09-23 10:22:33' from dual;
insert into t1 select '004','p1','tn1','tv1','on',sysdate,'2011-09-22 10:22:33' from dual;
insert into t1 select '005','p1','tn1','tv1','on',sysdate,'2011-09-20 10:22:33' from dual;
insert into t1 select '006','p3','tn1','tv1','on',sysdate,'2011-09-23 10:22:33' from dual;
insert into t1 select '007','p2','tn1','tv1','off',sysdate,'2011-08-23 10:22:33' from dual;
insert into t1 select '008','p1','tn1','tv1','off',sysdate,'2011-09-23 10:22:33' from dual;
insert into t1 select '009','p1','tn1','tv1','off',sysdate,'2011-09-21 10:22:33' from dual;

需求：根据字段 pc tname tstatus 分组获取数量，并关联到time2最大的那条记录

要求获取如下结果
id pc tname,tver,tstatus ,count,time2
001 p1 tn1 tv1 on 3 '2011-09-23 10:22:33'
001 p1 tn1 tv1 off 2 '2011-09-23 10:22:33'
001 p2 tn1 tv1 off 1 '2011-08-23 10:22:33'
001 p3 tn1 tv1 on 1 '2011-09-23 10:22:33'

需要效率高点的sql，因为t1表数据已经比较大，30w多数据，字段也比较多
自己写了个，有点长，但是感觉效率不好

[dawugui]

select t.* from t1 t where time2 = (select max(time2) from t1 where pc = t.pc and tname = t.tname and tstatus = t.tstatus) order by t.pc ,t.tname ,t.tstatus

select t.* from t1 t where not exists (select 1 from t1 where pc = t.pc and tname = t.tname and tstatus = t.tstatus and time2 > t.time2) order by t.pc ,t.tname ,t.tstatus

select 除了px字段外的其他字段 from 
(
  select m.* , row_number() over(partition by m.pc ,m.tname ,m.tstatus order by m.time2) px from t1 m
) n
where px = 1
order by n.pc ,n.tname ,n.tstatus

[iqlife]

贴出你的语句和执行计划看看，

试试用ROW_NUMBER，或者用用FIST_VALUE

select pc,tname,tstatus,count(*) as cnt,first_value() over(partition by m.pc ,m.tname ,m.tstatus order by m.time2) as max_timefrom m

[ETCentury]

没看懂楼主的意思。但发现楼主给的创建语句有问题

create table t1(
 id varchar2(20) primary key,
 pc varchar2(20),
 tname varchar2(20),
 tver varchar2(20),
 tstatus varchar2(20),
 time1 date,
 time2 date
);

insert into t1 select '002','p1','tn1','tv1','oooo',sysdate,to_date('2011-09-21 10:22:33','yyyy-MM-dd HH24:mi:ss') from dual;
insert into t1 select '003','p1','tn1','tv1','off',sysdate,to_date('2011-09-23 10:22:33','yyyy-MM-dd HH24:mi:ss') from dual;
insert into t1 select '004','p1','tn1','tv1','on',sysdate,to_date('2011-09-22 10:22:33','yyyy-MM-dd HH24:mi:ss') from dual;
insert into t1 select '005','p1','tn1','tv1','on',sysdate,to_date('2011-09-20 10:22:33','yyyy-MM-dd HH24:mi:ss') from dual;
insert into t1 select '006','p3','tn1','tv1','on',sysdate,to_date('2011-09-23 10:22:33','yyyy-MM-dd HH24:mi:ss') from dual;
insert into t1 select '007','p2','tn1','tv1','off',sysdate,to_date('2011-08-23 10:22:33','yyyy-MM-dd HH24:mi:ss') from dual;
insert into t1 select '008','p1','tn1','tv1','off',sysdate,to_date('2011-09-23 10:22:33','yyyy-MM-dd HH24:mi:ss') from dual;
insert into t1 select '009','p1','tn1','tv1','off',sysdate,to_date('2011-09-21 10:22:33','yyyy-MM-dd HH24:mi:ss') from dual;

[coolkisses]

疑问
1. 给出的希望结果中，为什么ID 都是 001? 这是怎么得来的？

2. 如果只是取最大的time，为什么不直接用 max,而一定要用关联的方式？

[dawugui]

[Quote=引用 4 楼 coolkisses 的回复:]
疑问
1. 给出的希望结果中，为什么ID 都是 001? 这是怎么得来的？

2. 如果只是取最大的time，为什么不直接用 max,而一定要用关联的方式？
[/Quote]
如果只是求id,time,则可用max.
但是他还需要其他字段,就要用到子查询了.

[dawugui]

--如果还需要求数量，则：
select m.* , n.cnt from 
(
  --以下两个任意一个
  select t.* from t1 t where time2 = (select max(time2) from t1 where pc = t.pc and tname = t.tname and tstatus = t.tstatus) 
  select t.* from t1 t where not exists (select 1 from t1 where pc = t.pc and tname = t.tname and tstatus = t.tstatus and time2 > t.time2) 
) m,
(
  select pc, tname ,tstatus , count(1) cnt from t1 group by pc ,tname ,tstatus
) n
where m.pc = n.pc and m.tname = n.tname , m.tstatus = n.tstatus
order by m.pc ,m.tname ,m.tstatus