sql-server - T-SQL 根据匹配条件/规则匹配两个表

Question

我正试图找到一种执行此匹配过程的有效方法。我不确定基于集合的更新是否可行，或者是否应该使用游标。我想将匹配规则放在一个单独的表中，如下所示(这样它们就不必在存储过程中进行硬编码)。这是在 SQL Server 2014 中。

我有两个表需要根据一组匹配规则/标准(在第三个表中)进行匹配。我正试图找到一种有效的方法来完成此任务。

情况是这样的:我们有需要与预订相匹配的付款 - 为了做到这一点，我们将有一组我们可以遵循的匹配规则。我需要根据这些规则匹配表，并使用 match_code(用于更新数据的规则)更新付款表中的数据。

付款表:

╔═══════════╦══════════╦══════════════╦════════════╦═══════╦════════════╗
║ FirstName ║ LastName ║ Confirmation ║    Date    ║ Trans ║ Match_code ║
╠═══════════╬══════════╬══════════════╬════════════╬═══════╬════════════╣
║ Scott     ║ Bloom    ║       123456 ║ 2016-01-15 ║       ║            ║
║ Beverly   ║ Smith    ║        65487 ║ 2016-08-16 ║       ║            ║
║ Cindy     ║ Plum     ║       147852 ║ 2016-07-19 ║       ║            ║
╚═══════════╩══════════╩══════════════╩════════════╩═══════╩════════════╝

预订表:

╔═══════════╦══════════╦══════════════╦════════════╦═════════════╗
║ FirstName ║ LastName ║ Confirmation ║    Date    ║ Transaction ║
╠═══════════╬══════════╬══════════════╬════════════╬═════════════╣
║ Alfred    ║ Kim      ║       987456 ║ 2016-11-17 ║       12345 ║
║ Beverly   ║ Smith    ║        65487 ║ 2016-07-14 ║       12346 ║
║ Cindy     ║ Plum     ║        99898 ║ 2016-07-19 ║       12347 ║
╚═══════════╩══════════╩══════════════╩════════════╩═════════════╝

规则表:

╔════════════╦═══════════╦══════════╦══════════════╦══════╗
║ Match_code ║ FirstName ║ LastName ║ Confirmation ║ Date ║
╠════════════╬═══════════╬══════════╬══════════════╬══════╣
║          1 ║         1 ║        1 ║            1 ║    1 ║
║          2 ║         1 ║        1 ║            1 ║    0 ║
║          3 ║         1 ║        1 ║            0 ║    1 ║
╚════════════╩═══════════╩══════════╩══════════════╩══════╝

规则表标识哪些字段需要被视为每个 Match_code 的匹配项

匹配程序应贯穿并尝试根据匹配规则将付款与预订匹配。

• 它会尝试根据匹配代码 1 进行更新，但发现所有列上都没有完全匹配。
• 它尝试匹配代码 2 并找到一个匹配项(Beverly 匹配第一个、最后一个、确认而非日期)。
• 它尝试匹配代码 3 并找到一个匹配项(Cindy 匹配第一个、最后一个日期，但不是确认)。

程序应根据在预订表中找到的匹配更新 payments.trans 字段和 payments.match_code 字段。结果将是这样的:

付款表:

╔═══════════╦══════════╦══════════════╦════════════╦═══════╦════════════╗
║ FirstName ║ LastName ║ Confirmation ║    Date    ║ Trans ║ Match_code ║
╠═══════════╬══════════╬══════════════╬════════════╬═══════╬════════════╣
║ Scott     ║ Bloom    ║       123456 ║ 2016-01-15 ║       ║          0 ║
║ Beverly   ║ Smith    ║        65487 ║ 2016-08-16 ║ 12346 ║          2 ║
║ Cindy     ║ Plum     ║       147852 ║ 2016-07-19 ║ 12347 ║          3 ║
╚═══════════╩══════════╩══════════════╩════════════╩═══════╩════════════╝

结果是，我们现在知道每笔付款与哪个预订匹配，以及与哪个匹配代码匹配。

如果您对完成此类任务的最佳方法有任何建议，我们将不胜感激 - 提前致谢!

Answer 1

假设您的规则是静态的并且永远不会改变(我知道情况并非如此，)我会做类似的事情:

WITH p (paymentID, rule1Key, rule2Key, rule3Key) AS 
(SELECT
paymentID, 
firstname + '|' + lastname + '|' + confirmation + '|' + [DATE] AS rule1Key,
firstname + '|' + lastname + '|' + confirmation  AS rule2Key,
firstname + '|' + lastname + '|' + [DATE] AS rule3Key
FROM
payment),

b (TRANSACTION, rule1Key, rule2Key, rule3Key) AS
(SELECT
[TRANSACTION], 
firstname + '|' + lastname + '|' + confirmation + '|' + [DATE] AS rule1Key,
firstname + '|' + lastname + '|' + confirmation  AS rule2Key,
firstname + '|' + lastname + '|' + [DATE] AS rule3Key
FROM
bookings)

UPDATE p
SET trans = 
    CASE 
        WHEN b1 IS NOT NULL THEN    b1.TRANSACTION
        WHEN b2 IS NOT NULL THEN    b2.TRANSACTION
        WHEN b3 IS NOT NULL THEN    b3.TRANSACTION
    end,
SET match_code = 
    CASE 
        WHEN b1 IS NOT NULL THEN    1
        WHEN b2 IS NOT NULL THEN    2
        WHEN b3 IS NOT NULL THEN    3
    end
FROM
p LEFT OUTER JOIN
b b1 ON
p.rule1key = b1.rule1key LEFT OUTER JOIN
b b2 ON
p.rule2key = b2.rule2key LEFT OUTER JOIN
b b3 ON
p.rule3key = b3.rule3key;

但是，请理解，您的恶意用户想要玩弄规则(他们不会喜欢他们当前的限制，他们稍后会要求 <、> 和 OR 条件，记住我的话!当他们要求 ( ) 你反抗!

然后您需要他们为您构建不同的表，以便您可以使用动态 sql 从规则表的内容构建查询。他们可以有一个包含 matchID 和 fieldName 的 2 列表，并假设它们始终由 AND 条件连接，因此您的规则如下所示:

matchID   fieldName
1         FirstName 
1         LastName 
1         Confirmation 
1         DATE
2         FirstName 
2         LastName 
2         Confirmation 
3         FirstName 
3         LastName 
3         DATE

希望您能了解如何从这些规则内容获取可以生成的查询以进行更新。