我现在有这样的数据
怎么用语句实现??
BH MC SL DCRQ
141 宝钢水库 黑颈鸊鷉 10 2007-2-1
142 宝钢水库 小鸊鷉 44 2007-2-1
143 宝钢水库 织女银鸥 2 2007-2-1
144 宝钢水库 环颈鸻 30 2007-2-1
145 宝钢水库 白腰草鹬 3 2007-2-1
146 宝钢水库 黑颈鸊鷉 6 2007-2-1在上面的数据中 141 和 146 实现结果想把 141 146合并并且数量还要想加 保留 相加的结果删除另外一条
怎么用语句实现??
BH MC SL DCRQ
141 宝钢水库 黑颈鸊鷉 10 2007-2-1
142 宝钢水库 小鸊鷉 44 2007-2-1
143 宝钢水库 织女银鸥 2 2007-2-1
144 宝钢水库 环颈鸻 30 2007-2-1
145 宝钢水库 白腰草鹬 3 2007-2-1
146 宝钢水库 黑颈鸊鷉 6 2007-2-1在上面的数据中 141 和 146 实现结果想把 141 146合并并且数量还要想加 保留 相加的结果删除另外一条
update a
set sl=(select sum(sl)
from a
where mc='宝钢水库 黑颈鸊鷉'
)
where bh=146然后再
delete *
from a
where bh=141
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>
update a a1 set sl=(select sum(sl)
from a
where mc=a1.mc)
where bh in (select min(bh) from a group by mc)
delete from a
where bh not in (select min(bh) from a group by mc)
insert into tbl select 142,N'BB',44,'2007-2-1' from dual;
insert into tbl select 143,N'CC',2,'2007-2-1' from dual;
insert into tbl select 144,N'DD',30,'2007-2-1' from dual;
insert into tbl select 145,N'EE',3,'2007-2-1' from dual;
insert into tbl select 146,N'AA',6,'2007-2-1' from dual;
insert into tbl select 147,N'AA',1,'2007-2-1' from dual;
insert into tbl select 148,N'BB',5,'2007-2-1' from dual;
select * from tbl;--结果:
141 AA 10 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
146 AA 6 2007-2-1
147 AA 1 2007-2-1
148 BB 5 2007-2-1
update tbl a1 set sl=(select sum(sl)
from tbl
where mc=a1.mc)
where bh in (select min(bh) from tbl group by mc)select * from tbl;--结果:
141 AA 17 2007-2-1
142 BB 49 2007-2-1
143 CC 2 2007-2-1
144 DD 30 2007-2-1
145 EE 3 2007-2-1
146 AA 6 2007-2-1
147 AA 1 2007-2-1
148 BB 5 2007-2-1
delete from tbl
where bh not in (select min(bh) from tbl group by mc)select * from tbl;--结果:
141 AA 17 2007-2-1
142 BB 49 2007-2-1
143 CC 2 2007-2-1
144 DD 30 2007-2-1
145 EE 3 2007-2-1