时间戳

首页 > 解决方案 > SQL在联接表中选择日期早于日期的记录

sql - SQL在联接表中选择日期早于日期的记录

问题描述

我有两个表，我需要能够双向更新它们，第一个表是项目列表：

项目

Item* | Rev* | RDate        | ECO  | New
------+------+--------------+------+----
A     | 0A   | 2019-01-01   | E123 | 1
A     | 01   | 2018-01-01   | E456 | 0
B     | 0A   | 2018-12-31   | E765 | 0
C     | 01   | 2018-10-25   | E456 | 0

第二个是父子表，有revisions，但是我要从Item表中填写Child Rev

树

Parent* | ParentRev* | Child* | ChildRev | VDate*
--------+------------+--------+----------+-----------
Y       | 0B         | C      | NULL     | 2019-01-01
Y       | 0C         | D      | NULL     | 2019-01-13
Z       | 01         | A      | NULL     | 2018-06-25
Z       | 02         | A      | NULL     | 2019-01-11
Z       | 0A         | B      | NULL     | 2019-01-01

笔记：

我研究了不同的问题，例如在每个 GROUP BY 组中选择第一行？，但我找不到一个在返回多个字段的连接表上使用基于行的条件。无论如何，我正在使用它来填写 ChildRev 所在的记录NULL，但它不包括该ECO列

UPDATE T 
SET [ChildRev] = (SELECT TOP 1 I.[Rev] AS [ChildRev]
                  FROM [Items] AS I
                  WHERE (I.[Item] = T.[Child]
                    AND I.[RDate] <= T.[VDate]) 
                  ORDER BY I.[RDate] DESC
                 )
FROM [Tree] AS T
WHERE T.[ChildRev] IS NULL

而且，这就是我得到的：

Parent | ParentRev | Child | ChildRev | VDate      | ECO
-------+-----------+-------+----------+------------+------
Y      | 0B        | C     | 01       | 2019-01-01 | NULL
Y      | 0C        | D     | NULL     | 2019-01-13 | NULL
Z      | 01        | A     | 01       | 2018-06-25 | NULL
Z      | 02        | A     | 0A       | 2019-01-11 | NULL
Z      | 0A        | B     | 0A       | 2019-01-01 | NULL

我正在处理 Tree 表中的 4.5M+ 记录和 Item 表中的 1.2M+ 记录，并且每天都在增长。我有两个问题：

  1. 有没有更好（更快）的方法来更新Tree表格？（如果包含 则奖励ECO）

    当我添加 newItems时，它们1在New字段中用 a 标记（可能使用触发器）

  2. 我将如何Tree使用新的检查/更新表Items

请注意，我无法真正控制数据加载的顺序（表格或日期）。

更新

那么，显然选择每个 GROUP BY 组中的第一行？基本上是解决方案，我只是没有意识到。特别是关于如何使用 CTE 更新我的数据表。感谢@Xedni 启发我；我只真正将 CTE 用于递归查询。所以，我最终得到了 2 个类似的 CTE，

  1. 当我向Tree表中添加新记录时，我添加了AND ChildRev IS NULL以限制更新：

    WITH CTE AS
(
    SELECT ...
)
UPDATE CTE
SET ChildRev = ItemRev
WHERE RID = 1
  AND ChildRev IS NULL

  2. 当我向Materials表中添加新记录时，我添加了一个WHERE...ANY子句：

    WITH CTE AS
(
    SELECT 
        ...
        RID = ROW_NUMBER() OVER (PARTITION BY t.Parent, t.ParentRev, t.Child 
                                 ORDER BY i.RDate DESC)
    FROM #Tree t
    JOIN #Items i
      ON t.Child = i.Item
     AND i.RDate <= t.VDate
    WHERE I.Process = ANY (SELECT Item FROM #Items WHERE New = 1)
)
UPDATE CTE
SET ChildRev = ItemRev
WHERE RID = 1

标签： sqlsql-serversql-server-2012greatest-n-per-group

解决方案

UPDATE您可以通过连接获得所需的值，而不是在子句中使用相关子查询。首先，创建一个看起来与相关子查询几乎相同的派生表，并获取所需的任何唯一值来识别#Items要与#Tree. 由于在提到的表上没有任何唯一约束的迹象，我不得不对此进行猜测。

设置样本数据

-- Setting up sample data
if object_id('tempdb.dbo.#Items') is not null drop table #Items
create table #Items
(
    Item char(1),
    Rev char(2),
    RDate date,
    ECO char(4),
    New bit
)

insert into #Items (Item, Rev, RDate, ECO, New)
values 
    ('A', '0A', '2019-01-01', 'E123', 1),
    ('A', '01', '2018-01-01', 'E456', 0),
    ('B', '0A', '2018-12-31', 'E765', 0),
    ('C', '01', '2019-01-01', 'E456', 0)

if object_id('tempdb.dbo.#Tree') is not null drop table #Tree
create table #Tree
(
    Parent char(1),
    ParentRev char(2),
    Child char(1),
    ChildRev char(2),
    VDate date,
    ECO char(4)
)
insert into #Tree (Parent, ParentRev, Child, ChildRev, VDate)
values
    ('Y', '0B', 'C', NULL, '2019-01-01'),
    ('Y', '0C', 'D', NULL, '2019-01-13'),
    ('Z', '01', 'A', NULL, '2018-06-25'),
    ('Z', '02', 'A', NULL, '2019-01-11'),
    ('Z', '0A', 'B', NULL, '2019-01-01')

现在您已经有了将行映射#tree到具有所需日期的行的派生表#items，再次将其连接到#items表以获取ECO,Rev以及您想要的任何其他内容。

-- Actual Update Statement
update a
set ChildRev = c.Rev,
    Eco = c.Eco
from #Tree a
-- Consruct a derived table basically mapping the rows in #tree to the rows with the desired dates you want.
inner join 
(
    select t.Child, t.ParentRev, MaxRDate = max(i.RDate)
    from #Tree t
    inner join #Items i
        on t.Child = i.Item
            and i.RDate <= t.VDate
    group by t.Child, t.ParentRev
) b
    on a.Child = b.Child
        and a.ParentRev = b.ParentRev
-- Finally, join the "intermidate mapping table" to #Items to get the values (eco, rev, etc.) you actually want
inner join #Items c
    on b.Child = c.Item
        and b.MaxRDate = c.RDate

select top 1000 *
from #Tree

一般来说，这可能比相关子查询执行得更好，尽管根据存在的索引，您的里程可能会有所不同。此外，如果您真的要处理 450 万条这样的记录，请考虑将其分成几批，或者想办法预先过滤您需要提前更新的内容。

至于在新行出现时启动此过程，您有两个选择。

  1. 在任何插入设置new标志的数据的过程中，让它同时启动这个过程（或者在同一个事务中做类似的事情）。
  2. Items如果这不是一个选项，理论上您可以使用桌面上的触发器执行相同的操作，并根据需要启动此过程。尽管 TBH 我推荐前者，因为它更容易在同一个地方包含您需要的所有逻辑，并且没有触发器的额外开销，这也有点混淆了保持数据同步的过程。

另一种选择

我刚刚制定的另一种方法是在单个查询中完成所有操作。使用带有row_numberRID 的 CTE（或派生表；随您所愿）。然后更新那个地方RID = 1

;with src as
(
    select 
        t.Parent,
        t.ParentRev,
        t.Child,
        t.ChildRev, 
        t.VDate,
        t.ECO,
        Item = i.Item,
        ItemRev = i.Rev,
        ItemRDate = i.RDate,
        ItemECO = i.ECO,
        ItemNew = i.NEW,
        RID = row_number() over (partition by t.Parent, t.ParentRev, t.Child order by i.RDate desc)
    from #Tree t
    inner join #Items i
        on t.Child = i.Item
            and i.RDate <= t.VDate
)
update src
set ECO = ItemECO,
    ChildREv = ItemRev
where RID = 1

推荐阅读