oracle数据库查询
请问大家一个问题:
假设有
表1 merchant,字段:MerID,MerName,saleAmt,taxAmt,SettID;(SettID与MerID是一对多的关系)
表2 MerSale,字段,MerID,MerName,saleAmt,taxAmt,SettID,RecordID;
要从merchant表中,选择出saleAmt>0的,放到另外一张表MerSale中,RecordID是一个序列号,相同SettID的记录拥有相同的RecordID.
假设数据为:
merchant:
A10086,null,1000,20,A10086;
A10087,null,400,10,A10086;
A10088,null,200,5,A10086;
B10000,null,1500,30,B10000;
B10001,null,1300,25,B10000;
C10007,null,5200,80,C10007;
C10008,null,4500,64,C10007;
执行查询语句后,MerSale中的数据为:
A10086,null,1000,20,A10086,1;
A10087,null,400,10,A10086,1;
A10088,null,200,5,A10086,1;
B10000,null,1500,30,B10000,2;
B10001,null,1300,25,B10000,2;
C10007,null,5200,80,C10007,3;
C10008,null,4500,64,C10007,3;
------解决方案--------------------
rank()
------解决方案--------------------
update MerSale set RecordID = nvl((select count(1) from merchant where SettID = MerSale.MerID),0)