数据表有四个列:ID,batch,round,edate;
a, 1 , 1 , ....,
a, 1 , 2 ,.....,
a, 2 , 1 , ....,
..................
要计算同一个Id,每batch的最大round的总和,
比如计算ID为a 的round总和,batch为1的round最大为2,
batch为2的round最大为1,round=2+1.
用SQL语句查询出来,恳请高手指点一二....
------解决方案--------------------
select p.id,sum(p.round) from(
select id,batch,max(round) round from tableName group by id,batch) p group by p.id
------解决方案--------------------
我也弄一个.
- SQL code
drop table t;create table t (ID varchar2(10),batch varchar2(10),round number(10),edate date);insert into t values('a','1',1,sysdate);insert into t values('a','1',2,sysdate);insert into t values('a','2',3,sysdate);insert into t values('a','2',4,sysdate);insert into t values('a','3',4,sysdate);insert into t values('a','4',4,sysdate);insert into t values('b','1',4,sysdate);insert into t values('b','2',4,sysdate);select id,sum(round) from t a where (select count(*) from t b where b.round>a.round and b.id=a.id and b.batch=a.batch)=0 group by id;