Server离开外部联接，开始时大小写不停止

Question

我希望在左外部联接的'on‘子句中的一个大小写表达式中获得一个与第一个匹配的" when“匹配的行，但是当匹配时，我会从每个获得行。

互联网告诉我，这是不可能的，一个案件总是在第一次匹配时停止。

SELECT MILL_ORDER_NUMBER
       ,SHORTY_NAME
       ,PRIMARY_DEST
       ,ALT_DESTINATION
       ,CB.CDE_CNSUM_LOC as CB4V_CNSUM_LOC
       ,CB.CDE_DEST
       ,CB.NAM_CUST_SHTY
FROM HLFOR01A OA 

left outer join (select CDE_CNSUM_LOC, CDE_DEST, NAM_CUST_SHTY from CSAR_CB4V0023) CB

on case
when ((OA.SHORTY_NAME = CB.NAM_CUST_SHTY) and (substring(OA.PRIMARY_DEST,1,1) < 'A') and (OA.PRIMARY_DEST = CB.CDE_DEST)) then 1

when ((OA.SHORTY_NAME = CB.NAM_CUST_SHTY) and (CB.CDE_DEST = (select min(dd.CDE_DEST) from CSAR_CB4V0023 dd where dd.NAM_CUST_SHTY = OA.SHORTY_NAME))) then 1
else 0  end = 1
where MILL_ORDER_NUMBER = '84220631'

如果这两种情况都存在的话，我会

MILL_ORDER_NUMBER SHORTY_NAME PRIMARY_DEST ALT_DESTINATION CB4V_CNSUM_LOC CDE_DEST NAM_CUST_SHTY

84220631      CMPNY1    5U              1641        00      CMPNY1 <-- matches 2nd when clause

84220631      CMPNY1    5U              1627        5U      CMPNY1 <-- matches 1st when clause

如果我注释掉第一个when子句，我只得到第一行。如果我注释掉第二句时，我只得到第二行。

我不明白为什么它不停止在第一次当子句匹配？

Answer 1

在连接时，连接一侧的所有行都将根据联接另一侧的所有行进行计算。

您的case语句在第一次匹配时停止，对两边的每一对行都是如此。仅仅因为左边的一行已经匹配了右边的一行，就不会阻止它匹配右边的另一行，使用case语句中的任一种情况，因为每一对都是独立于任何现有匹配项计算的。您的case语句实际上相当于，但效率低于：

on OA.SHORTY_NAME = CB.NAM_CUST_SHTY
    and ((substring(OA.PRIMARY_DEST,1,1) < 'A' and OA.PRIMARY_DEST = CB.CDE_DEST) 
    or (CB.CDE_DEST = (select min(dd.CDE_DEST) from CSAR_CB4V0023 dd where dd.NAM_CUST_SHTY = OA.SHORTY_NAME))

把它看作是嵌套在另一个for循环中的for循环，在每一对可能的行上执行case语句。

DECLARE @Table (MILL_ORDER_NUMBER {type}, SHORTY_NAME {type}, PRIMARY_DEST {type}, ALT_DESTINATION {type}, CB4V_CNSUM_LOC {type}, CDE_DEST {type}, NAM_CUST_SHTY {type})

INSERT INTO @Table (MILL_ORDER_NUMBER ,SHORTY_NAME ,PRIMARY_DEST ,ALT_DESTINATION ,CB4V_CNSUM_LOC, CDE_DEST, NAM_CUST_SHTY)
SELECT MILL_ORDER_NUMBER, SHORTY_NAME, PRIMARY_DEST, ALT_DESTINATION, CB.CDE_CNSUM_LOC, CB.CDE_DEST, CB.NAM_CUST_SHTY
FROM HLFOR01A OA 
JOIN (select CDE_CNSUM_LOC, CDE_DEST, NAM_CUST_SHTY from CSAR_CB4V0023) CB
on ((OA.SHORTY_NAME = CB.NAM_CUST_SHTY) and (CB.CDE_DEST = (select min(dd.CDE_DEST) from CSAR_CB4V0023 dd where dd.NAM_CUST_SHTY = OA.SHORTY_NAME)))
where MILL_ORDER_NUMBER = '84220631'

INSERT INTO @Table (MILL_ORDER_NUMBER ,SHORTY_NAME ,PRIMARY_DEST ,ALT_DESTINATION ,CB4V_CNSUM_LOC, CDE_DEST, NAM_CUST_SHTY)
SELECT MILL_ORDER_NUMBER, SHORTY_NAME, PRIMARY_DEST, ALT_DESTINATION, CB.CDE_CNSUM_LOC, CB.CDE_DEST, CB.NAM_CUST_SHTY
FROM HLFOR01A OA 
JOIN (select CDE_CNSUM_LOC, CDE_DEST, NAM_CUST_SHTY from CSAR_CB4V0023) CB
on ((OA.SHORTY_NAME = CB.NAM_CUST_SHTY) and (substring(OA.PRIMARY_DEST,1,1) < 'A') and (OA.PRIMARY_DEST = CB.CDE_DEST))
where MILL_ORDER_NUMBER = '84220631' AND NOT EXISTS (SELECT top 1 1 FROM @table t where t.MILL_ORDER_NUMBER=OA.MILL_ORDER_NUMBER)

INSERT INTO @Table (MILL_ORDER_NUMBER ,SHORTY_NAME ,PRIMARY_DEST ,ALT_DESTINATION)
SELECT MILL_ORDER_NUMBER, SHORTY_NAME, PRIMARY_DEST, ALT_DESTINATION
FROM HLFOR01A OA 
where MILL_ORDER_NUMBER = '84220631' AND NOT EXISTS (SELECT top 1 1 FROM @table t where t.MILL_ORDER_NUMBER=OA.MILL_ORDER_NUMBER)

SELECT MILL_ORDER_NUMBER ,SHORTY_NAME ,PRIMARY_DEST ,ALT_DESTINATION ,CB4V_CNSUM_LOC, CDE_DEST, NAM_CUST_SHTY FROM @Table

Answer 2

这种情况将产生一组记录，这些记录与产生1的任何一个时间匹配。要获得第一个匹配记录，您可以执行一个顶级n：

SELECT TOP 1 MILL_ORDER_NUMBER ...

一个组by可以让你在CB4V_CNSUM_LOC、CB.CDE_DEST、CB.NAM_CUST_SHTY上使用最小或最大的结果行，但是您可能要从多个记录中混合这些结果，所以这可能不是您想要的结果。

对第一个选项的调整将是“加权”您的情况下的每个时间，这样您将得到一个与第一个行匹配的行(如果它存在的话)：

SELECT TOP 1 MILL_ORDER_NUMBER ...
...
ORDER BY
case
when ((OA.SHORTY_NAME = CB.NAM_CUST_SHTY) and (substring(OA.PRIMARY_DEST,1,1) < 'A') and (OA.PRIMARY_DEST = CB.CDE_DEST)) 
then 1
when ((OA.SHORTY_NAME = CB.NAM_CUST_SHTY) and (CB.CDE_DEST = (select min(dd.CDE_DEST) from CSAR_CB4V0023 dd where dd.NAM_CUST_SHTY = OA.SHORTY_NAME))) 
then 2
else 99  end

Answer 3

这个对你有用吗？

SELECT TOP 1 MILL_ORDER_NUMBER
       ,SHORTY_NAME
       ,PRIMARY_DEST
       ,ALT_DESTINATION
       ,CB.CDE_CNSUM_LOC as CB4V_CNSUM_LOC
       ,CB.CDE_DEST
       ,CB.NAM_CUST_SHTY
FROM HLFOR01A OA 
left outer join (select CDE_CNSUM_LOC, CDE_DEST, NAM_CUST_SHTY from CSAR_CB4V0023) CB on 
    ((OA.SHORTY_NAME = CB.NAM_CUST_SHTY) and (substring(OA.PRIMARY_DEST,1,1) < 'A') and (OA.PRIMARY_DEST = CB.CDE_DEST)) 
    or
    ((OA.SHORTY_NAME = CB.NAM_CUST_SHTY) and (CB.CDE_DEST = (select min(dd.CDE_DEST) from CSAR_CB4V0023 dd where dd.NAM_CUST_SHTY = OA.SHORTY_NAME)))
where MILL_ORDER_NUMBER = '84220631'

我意识到有些父母是多余的，但为了一致起见，他们一直呆在家里。我不能百分之百肯定这就是你想要的，但是.

前1将获得第一个结果(虽然没有ORDER BY子句，但它是任意的，结果将是第一个)。

新的ON子句有点简洁，我认为它代表了您想要的；如果不是，它至少应该更容易可视化和操作。

我希望这能帮到你。