update 有关问题

update 问题
我现在有这样的数据


怎么用语句实现？？
BH MC SL DCRQ
141宝钢水库黑颈鸊鷉102007-2-1
142宝钢水库小鸊鷉442007-2-1
143宝钢水库织女银鸥22007-2-1
144宝钢水库环颈鸻302007-2-1
145宝钢水库白腰草鹬32007-2-1
146宝钢水库黑颈鸊鷉62007-2-1

在上面的数据中 141 和 146

实现结果想把 141 146合并

并且数量还要想加 保留 相加的结果

删除另外一条

[解决办法]

SQL code

SQL> create table tbl(bh int,MC nvarchar2(30),sl int,dcrq nvarchar2(10));Table createdSQL> SQL> insert into tbl select 141,N'AA',10,'2007-2-1' from dual;1 row insertedSQL> insert into tbl select 142,N'BB',44,'2007-2-1' from dual;1 row insertedSQL> insert into tbl select 143,N'CC',2,'2007-2-1' from dual;1 row insertedSQL> insert into tbl select 144,N'DD',30,'2007-2-1' from dual;1 row insertedSQL> insert into tbl select 145,N'EE',3,'2007-2-1' from dual;1 row insertedSQL> insert into tbl select 146,N'AA',6,'2007-2-1' from dual;1 row insertedSQL> 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-1SQL>