求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 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
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;
1. 给出的希望结果中,为什么ID 都是 001? 这是怎么得来的?2. 如果只是取最大的time,为什么不直接用 max,而一定要用关联的方式?
但是他还需要其他字段,就要用到子查询了.
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