后续问题:比较Oracle表中的行并更新匹配行 [英] Followup Q: Compare Rows in Oracle Table and update Matching Rows
问题描述
一周前，我问了以下问题:
I asked the following a week ago:
我收到了非常有用的有用答案，但是由于需要修改收到的答案并添加另一个过滤器，我再次陷入困境.
I received excellent helpful answers, but i am stuck again, as i need to amend the answers received, and add another filter.
除了具有不同的BuySell字段外，还有一个Description字段，该字段不能相等. 与BuySell字段只能接受两个值不同，Description可以是任何值.
On top of having different BuySell fields, there is a Description field that cannot be equal. Unlike BuySell field that can only take two values, Description can be anything.
以下是我一直在使用的@ MT0的答案，我希望对此进行修改:
Following is the answer by @MT0 i have been using, and that i hope to amend:
MERGE INTO your_table dst
USING ( SELECT ROW_NUMBER() OVER (
PARTITION BY tDate, Product, Price, Quantity, BuySell
ORDER BY ID
) AS idx,
COUNT( CASE BuySell WHEN 'Buy' THEN 1 END ) OVER (
PARTITION BY tDate, Product, Price, Quantity
) AS num_buy,
COUNT( CASE BuySell WHEN 'Sell' THEN 1 END ) OVER (
PARTITION BY tDate, Product, Price, Quantity
) AS num_sell FROM your_table
) src ON ( src.ROWID = dst.ROWID AND src.idx <= LEAST( src.num_buy, src.num_sell ) )
WHEN MATCHED THEN UPDATE SET Status = 'Matched';
以下是一个示例
我有一个如下表格:
**ID tDate Product Description Price Quantity BuySell Status**
1 10-May-17 pppp p1 $12 20 Buy Null
2 12-May-17 tttt t1 $10 20 Sell Null
3 12-May-17 tttt t2 $10 20 Buy Null
4 18-May-17 pppp p2 $14 20 Sell Null
5 18-May-17 pppp p3 $14 20 Buy Null
6 19-May-17 xxxx x4 $11 10 Sell Null
7 19-May-17 xxxx x4 $11 10 Buy Null
我需要更新名为STATUS的字段，并将其设置为匹配"，只要找到一对具有相等tDate，产品，价格和数量且不等于BuySell AND Description的对.
I need to update the field named STATUS, and set it to 'Matched', wherever a pair is found with equal tDate, product, price, and quantity, and NOT equal BuySell AND Description.
以下是理想的结果:
**ID tDate Product Description Price Quantity BuySell Status**
1 10-May-17 pppp p1 $12 20 Buy Null
2 12-May-17 tttt t1 $10 20 Sell Matched
3 12-May-17 tttt t2 $10 20 Buy Matched
4 18-May-17 pppp p2 $14 20 Sell Matched
5 18-May-17 pppp p3 $14 20 Buy Matched
6 19-May-17 xxxx x4 $11 10 Sell Null
7 19-May-17 xxxx x4 $11 10 Buy Null
请注意6和7不匹配，因为它们具有相同的说明.
Notice that 6 and 7 did not match because they have the same Description.
推荐答案
update your_table
set status='Matched'
where id in(
with IDS(id1,id2,grp,num,rnum) as(
select a.id,b.id,
rank() over(order by a.tdate,a.price,a.product,a.quantity),
case when a.id=lag(a.id) over(partition by a.tdate,a.price,a.product,a.quantity order by a.id)
then 0
else dense_rank() over(partition by a.tdate,a.price,a.product,a.quantity order by a.id) end,
row_number() over(partition by a.tdate,a.price,a.product,a.quantity order by a.id)
from your_table a, your_table b
where a.tdate=b.tdate and a.price=b.price and a.product=b.product
and a.quantity=b.quantity
and a.buysell='Sell' and b.buysell='Buy' and a.description!=b.description
),
Q(id1,id2,grp,num,used) as(
select id1,id2,grp,num,','||id2||','
from IDS where rnum=1
union all
select I.id1,R.column_value,Q.grp,Q.num+1,Q.used||R.column_value||','
from Q, IDS I,
table(cast(multiset(
select min(id2) id from IDS N
where N.id1=I.id1
and Q.used not like '%,'||N.id2||',%'
) as sys.ODCINumberList)) R
where I.grp=Q.grp and I.num=Q.num+1
)
select decode(X,1,id1,id2)
from Q,
(select 1 X from DUAL union all select 2 from DUAL)
where id2 is not null
)
这篇关于后续问题:比较Oracle表中的行并更新匹配行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
