日期:2014-05-17 浏览次数:20910 次
SQL> create table tbl(bh int,MC nvarchar2(30),sl int,dcrq nvarchar2(10));
Table created
SQL>
SQL> insert into tbl select 141,N'AA',10,'2007-2-1' from dual;
1 row inserted
SQL> insert into tbl select 142,N'BB',44,'2007-2-1' from dual;
1 row inserted
SQL> insert into tbl select 143,N'CC',2,'2007-2-1' from dual;
1 row inserted
SQL> insert into tbl select 144,N'DD',30,'2007-2-1' from dual;
1 row inserted
SQL> insert into tbl select 145,N'EE',3,'2007-2-1' from dual;
1 row inserted
SQL> insert into tbl select 146,N'AA',6,'2007-2-1' from dual;
1 row inserted
SQL>
SQL> select bh,mc,slnew,dcrq from (
2 select bh,mc,slnew,dcrq,row_number()over(partition by mc,dcrq order by slnew desc) rw from (
3 select bh,mc,sl,dcrq,sum(sl)over(partition by mc,dcrq order by bh) SLnew from tbl
4 )
5 ) where rw=1;
BH MC SLNEW DCRQ
--------------------------------------- ------------------------ ---------- --------------------
146 AA 16 2007-2-1
142 BB 44 2007-2-1
143 CC 2 2007-2-1
144 DD 30 2007-2-1
145 EE 3 2007-2-1
SQL>