sql—如何连接两个表并只保留最新的记录

vawmfj5a  于 2021-07-29  发布在  Java
关注(0)|答案(3)|浏览(899)

我想加入两个表。一个表是库存的历史记录,它的“最后更新”日期与库存的每个“部分”相关联。另一张table上有每一件的价格。我想把这些表合并起来,这样我就可以得到每一个价格的历史记录。如。

TABLE 1
Date         Item        Location           QTY 
06/01/2020   ABC         123                10
06/01/2020   DEF         234                12
06/02/2020   ABC         345                13
06/06/2020   ABC         123                10

                  TABLE 2
ITEM         Price
ABC          34.5
DEF          52.12

-----------------> result table ------------------>
Date         Item        Location           QTY       Price
06/01/2020   DEF          234               12        34.5
06/02/2020   ABC          345               13        52.12
06/06/2020   ABC          123               10        34.5

其中结果表进行筛选,以便只保留最新的记录。表1每分钟更新一次,以显示新的库存水平。项目+位置组合是“唯一的”,因为表1处于项目/位置粒度级别。但是,当表更新并创建新条目时,可能会有许多相同的项/位置组合(它是一个历史表,因此具有相同项+位置组合的旧条目仍保留在表中)。有时日期不同,有时日期是同一天。
我写的这个问题是:

SELECT DISTINCT
 TB1.DATE
,TB1.ITEM
,TB1.LOCATION
,TB1.QTY
,TB2.ITEM_COST

FROM
(
SCHEMA_1.TABLE1 AS TB1
JOIN  SCHEMA_1.TABLE2 AS TB2

ON TB1.ITEM = TB2.ITEM
JOIN (
      SELECT ITEM AS ITM,
          LOCATION AS LOC,
          MAX(DATE) AS MAXDATE
          FROM SCHEMA_1.TABLE1
          GROUP BY ITEM, LOCATION
      )TB3
  ON TB1.ITEM = TB3.ITM AND TB1.LOCATION= TB3.LOC AND TB1.DATE= TB3.MAXDATE
)

这个查询确实执行了,但是它给了我重复的结果,并且绝对不会只过滤最近的记录。不知道我做错了什么。

vql8enpb

vql8enpb1#

好的旧的subselect也应该起作用。假设每个项目的unqiqe日期、位置对。

SELECT T1.* , T2.price 
FROM SCHEMA_1.TABLE1 AS TB1
JOIN SCHEMA_1.TABLE2 AS TB2 ON TB1.Item = TB2.Item
WHERE Date = (SELECT MAX(Date) FROM SCHEMA_1.TABLE1 AS TB3
              WHERE TB1.Item = TB3.Item
              AND   TB1.Location = TB3.Location)
eaf3rand

eaf3rand2#

我建议:

SELECT t1.*, t2.ITEM_PRICE
FROM SCHEMA_1.TABLE1 t1 JOIN
     (SELECT t2.ITEM, t2.LOCATION,
             MAX(t2.ITEM_PRICE) KEEP (DENSE_RANK FIRST ORDER BY t2.DATE DESC) as ITEM_PRICE
      FROM SCHEMA_1.TABLE2 t2
      GROUP BY t2.ITEM, t2.LOCATION
     ) t2
     USING (ITEM, LOCATION);

oracle具有方便的功能来获取组中的“first”或“last”值。 KEEP 这不是最简单的语法,但它完全符合您的要求。

6za6bjd0

6za6bjd03#

列名(dte=date,loc=location)已更改,但您可以尝试此简单查询以获得结果:

Select dte dates, item, loc Locations, price, qty from 
(Select a.dte, a.item, a.loc,  b.price, a.qty,
max(a.dte) OVER (PARTITION BY a.item, a.loc) latest_dt 
from table1 a LEFT JOIN table2 b ON a.item = b.item) where dte = latest_dt
order by 1;

输出:

+-----------+------+-----------+-------+-----+
|   DATES   | ITEM | LOCATIONS | PRICE | QTY |
+-----------+------+-----------+-------+-----+
| 01-JUN-20 | DEF  | 234       | 52.12 | 12  |
+-----------+------+-----------+-------+-----+
| 02-JUN-20 | ABC  | 345       | 34.5  | 13  |
+-----------+------+-----------+-------+-----+
| 06-JUN-20 | ABC  | 123       | 34.5  | 10  |
+-----------+------+-----------+-------+-----+

您还可以将最新日期设置为:max(a.dte)keep(dense\u rank first order by dte desc)over(partition by a.item,a.loc)

相关问题