求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多数据,字段也比较多
自己写了个,但是效率不高,求个效率高的,谢谢
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多数据,字段也比较多
自己写了个,但是效率不高,求个效率高的,谢谢
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 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 m.pc ,m.tname ,m.tstatus
(
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
如果mssql大家可以帮你
from t1
group by pc,tname,tstatus
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