时间戳

首页 > 解决方案 > 首先在最低级别/最详细的字段值上加入查找表

sql - 首先在最低级别/最详细的字段值上加入查找表

问题描述

我有两个表，我试图基于 SQL Server 2014 中的单个字段离开联接，但问题是这个单个字段包含一个与主表中不同列中出现的四个“级别”之一相关的值. 该SalesOrg字段也是键的一部分，因为在事务表和查找表中都可以有多个销售组织。

这是主要的事务表：

SalesOrg     PHLevel1    PHLevel2    PHLevel3    PHLevel4    SalesQty
-----------------------------------------------------------------------
AB1          Z000        Y000        X000        V000        20
AB1          Z000        Y000        X000        V001        30
AB1          Z001        Y000        X001        V001        10
AB1          Z001        Y001        X000        V003        45
AB1          Z001        Y001        X000        V004        48
AB1          Z002        Y003        X002        V005        67

这是我试图加入事务表的查找表：

SalesOrg     PH              PHLevel      SalesRep
--------------------------------------------------
AB1          <null>          0            Mr. Pink
AB1          Z000            1            Mr. White
AB1          Z001            1            Mr. White
AB1          Z001Y000        2            Mr. Orange
AB1          Z001Y001        2            Mr. Orange
AB1          Z001Y001X000    3            Mr. Blonde

所以应该发生的是“最低”（即最高PHLevel，3）应该首先匹配，即：

SalesOrg     PHLevel1    PHLevel2    PHLevel3    PHLevel4    SalesQty    SalesRep
---------------------------------------------------------------------------------
AB1          Z000        Y000        X000        V000        20
AB1          Z000        Y000        X000        V001        30
AB1          Z001        Y000        X001        V001        10
AB1          Z001        Y001        X000        V003        45          Mr. Blonde
AB1          Z001        Y001        X000        V004        48          Mr. Blonde
AB1          Z002        Y003        X002        V005        67

然后我们需要匹配尚未使用级别 3 匹配的级别 2：

SalesOrg     PHLevel1    PHLevel2    PHLevel3    PHLevel4    SalesQty    SalesRep
---------------------------------------------------------------------------------
AB1          Z000        Y000        X000        V000        20
AB1          Z000        Y000        X000        V001        30
AB1          Z001        Y000        X001        V001        10          Mr. Orange
AB1          Z001        Y001        X000        V003        45          Mr. Blonde
AB1          Z001        Y001        X000        V004        48          Mr. Blonde
AB1          Z002        Y003        X002        V005        67

然后我们需要匹配尚未使用级别 2 或 3 匹配的级别 1：

SalesOrg     PHLevel1    PHLevel2    PHLevel3    PHLevel4    SalesQty    SalesRep
---------------------------------------------------------------------------------
AB1          Z000        Y000        X000        V000        20          Mr. White
AB1          Z000        Y000        X000        V001        30          Mr. White
AB1          Z001        Y000        X001        V001        10          Mr. Orange
AB1          Z001        Y001        X000        V003        45          Mr. Blonde
AB1          Z001        Y001        X000        V004        48          Mr. Blonde
AB1          Z002        Y003        X002        V005        67

最后，对于任何剩余的项目，我们需要检查级别 0，这是“不匹配”条目的默认值：

SalesOrg     PHLevel1    PHLevel2    PHLevel3    PHLevel4    SalesQty    SalesRep
---------------------------------------------------------------------------------
AB1          Z000        Y000        X000        V000        20          Mr. White
AB1          Z000        Y000        X000        V001        30          Mr. White
AB1          Z001        Y000        X001        V001        10          Mr. Orange
AB1          Z001        Y001        X000        V003        45          Mr. Blonde
AB1          Z001        Y001        X000        V004        48          Mr. Blonde
AB1          Z002        Y003        X002        V005        67          Mr. Pink

如果在事务表中创建连接字段以更容易匹配查找表，则没有问题。

我不确定扩展查找表是否有利，以便该字段包含“完全限定”的值（PH即PH始终包含///PHLevel1值PHLevel2，例如），但这可能会使查找表变得巨大，然后可能减慢连接（因为事务表可能包含一百万行）。PHLevel3PHLevel4Z001Y000X001V001

我不知道如何开始，因为我已经看到了其他带有层次结构的示例，但是查找表并不完全是一个层次结构——请问在 SQL 中有什么优雅的解决方案吗？

标签： sqlsql-serverjoin

解决方案

当我看到这样的代码时，我有点焦虑，但在您的情况下，它似乎反映了真正的需求，假设您无法更改表的底层结构：

select      t.*,
            SalesRep = coalesce(l4.SalesRep, l3.SalesRep, l2.SalesRep, l1.SalesRep, l0.SalesRep)
from        @transactionals t
left join   @lookup l4 on t.SalesOrg = l4.SalesOrg and l4.PH = t.PHLevel1 + t.PHLevel2 + t.PHLevel3 + t.PHLevel4 
left join   @lookup l3 on t.SalesOrg = l3.SalesOrg and l3.PH = t.PHLevel1 + t.PHLevel2 + t.PHLevel3
left join   @lookup l2 on t.SalesOrg = l2.SalesOrg and l2.PH = t.PHLevel1 + t.PHLevel2 
left join   @lookup l1 on t.SalesOrg = l1.SalesOrg and l1.PH = t.PHLevel1 
left join   @lookup l0 on t.SalesOrg = l0.SalesOrg and l0.PH is null

经过多一点考虑，我认为另一种策略会奏效。正如您所怀疑的，您可以连接事务表中的级别。然后你可以根据like 语句进行匹配。当然，这会让你得到太多的匹配，所以你使用一个窗口函数来找到匹配之间的最大 phLevel。然后查询 phLevel 等于最大值的位置。

with

    leadMatches as (

        select      t.*, 
                    l.SalesRep,
                    l.PHLevel,
                    maxPhLevel = max(l.phLevel) over (partition by ap.ph)
        from        @transactionals t
        cross apply (select PH = PHLevel1 + PHLevel2 + PHLevel3 + PHLevel4) ap
        join        @lookup l on t.SalesOrg = l.SalesOrg and ap.ph like isnull(l.ph,'') + '%'

    )

    select      m.SalesOrg, m.PHLevel1, m.PHLevel2, m.PHLevel3, m.PHLevel4, 
                m.SalesQty, m.SalesRep
    from        leadMatches m
    where       PHLevel = maxPhLevel

推荐阅读