从视图减去多个最大值

Question

我们有一个名为：vu_pohyby的视图。它包含所有进出口货物。它有很多记录。

它看起来像这样，例如：

RID         |kod_o |datum_p    |kod_id  |partner |pocet |Cont.n.|storno|cena_dokl
0032000015044 |01    |02.10.2020 |566     |248     |360   |545    |0     |0,250
0292000046277 |      |03.10.2020 |566     |326     |-6    |539    |0     |0,260
0292000046310 |      |04.10.2020 |566     |326     |-6    |533    |0     |0,260
0292000046338 |      |05.10.2020 |566     |325     |-6    |515    |0     |0,260
0292000046350 |      |05.10.2020 |566     |326     |-6    |509    |0     |0,260
-20 |      |05.10.2020 |566     |        |350   |509    |0     |0,000

Continuous number -> 
(sum (decode(STORNO, 0, decode(L.RID, '-20', 0,l.pocet),0)) over (ORDER BY l.datum_p ROWS UNBOUNDED PRECEDING))

我想知道价格之间的差额->收入/支出在另一张表中。

所以我想选择两个价格，它们与最高日期(datum_p(相关。

我试过了，但不好：

(select price_out from ( 
select l.kod_id,  --goods
l.cena_dokl as price_out
from vu_pohyby l --table
where l.storno=0 and l.kod_o is null --canceled=0, l.kod_o->flag=out/incoming
and substr(l.rid,1,3)='029' -- 029->out
group by l.rid, l.datum_p, l.kod_id, l.cena_dokl
order by l.datum_p desc ) 
aa where aa.kod_id=sklad_karta.id and rownum=1)
-
(select price_inc from ( 
select l.kod_id,
l.cena_dokl as price_inc
from vu_pohyby l 
where l.storno=0 
and substr(l.rid,1,3)='003' --003->incoming
group by l.rid, l.datum_p, l.kod_id, l.cena_dokl
order by l.datum_p desc ) 
aa where aa.kod_id=sklad_karta.id and rownum=1)

简化：

我想分散2个价格：

1:。。。其中MAX(datum_P(，kod_o＝NULL，storno＝0->在这种情况下0292000046350->0,26

2:。。。其中MAX(datum_P(，kod_o=01，storno=0->在这种情况下0032000015044->0,25差异：0,01

如何在一个查询中解决此问题？

Answer 1

我认为您希望使用MAX( cena_dokl ) KEEP ( DENSE_RANK LAST ORDER BY datum_p )来获取最新日期的值，然后您希望筛选价格输入/价格输出行。类似于：

SELECT kod_id,
MAX( CASE WHEN kod_o IS NULL THEN datum_p END )
AS date_most_recent_price_out,
MAX( CASE WHEN kod_o IS NULL THEN cena_dokl END ) KEEP (
DENSE_RANK LAST
ORDER BY CASE WHEN kod_o IS NULL THEN datum_p END ASC NULLS FIRST
) AS most_recent_price_out,
MAX( CASE WHEN kod_o IS NOT NULL THEN datum_p END )
AS date_most_recent_price_in,
MAX( CASE WHEN kod_o IS NOT NULL THEN cena_dokl END ) KEEP (
DENSE_RANK LAST
ORDER BY CASE WHEN kod_o IS NOT NULL THEN datum_p END ASC NULLS FIRST
) AS most_recent_price_in
FROM   vu_pohyby
WHERE  storno=0
AND    (  ( kod_o IS NULL     AND rid LIKE '029%' )
OR ( kod_o IS NOT NULL AND rid LIKE '003%' ) )
GROUP BY
kod_id

对于您的测试数据：

CREATE TABLE vu_pohyby ( RID, kod_o, datum_p, kod_id, partner, pocet, "Cont.n.", storno, cena_dokl ) AS
SELECT '0032000015044', '01', DATE '2010-10-02', 566, 248,  360, 545, 0, 0.250 FROM DUAL UNION ALL
SELECT '0292000046277', NULL, DATE '2010-10-03', 566, 326,   -6, 539, 0, 0.260 FROM DUAL UNION ALL
SELECT '0292000046310', NULL, DATE '2010-10-04', 566, 326,   -6, 533, 0, 0.260 FROM DUAL UNION ALL
SELECT '0292000046338', NULL, DATE '2010-10-05', 566, 325,   -6, 515, 0, 0.260 FROM DUAL UNION ALL
SELECT '0292000046350', NULL, DATE '2010-10-05', 566, 326,   -6, 509, 0, 0.260 FROM DUAL UNION ALL
SELECT '          -20', NULL, DATE '2010-10-05', 566, NULL, 350, 509, 0, 0.000 FROM DUAL

输出：

KOD_ID|DATE_MOST_RECENT_PRICE_OUT|MOST_RECENT_RICE_OUT| DATE_MOST_RECENT_PRICE_IN|MOST/RECENT_RICe_IN-----：|：----------------------------------|----------------------------------:|：------------------------------|--------------------：566|2010-10-05 |.26|2010-10-02 |.25

db<gt；小提琴这里