时间戳

首页 > 解决方案 > WHERE NOT EXISTS 条款仅部分起作用

sql - WHERE NOT EXISTS 条款仅部分起作用

问题描述

下午好，

我目前正在运行一个带有 WHERE NOT EXISTS 的查询，它可以解决我的大多数问题，但某些行正在被跳过。我一直在努力解决这个问题大约三个小时，但没有运气。这是我的表结构。

ContractItems（特定客户的特定合同中的项目。）

| Manufacturer | Style | Color | Size | Sort |
|--------------|-------|-------|------|------|
| LIB          | 586M  | FL    |      |    0 |
| LIB          | 586M  | FL    | 2XL  |    6 |
| LIB          | 586M  | FL    | 3XL  |    7 |
| SSA          | 08700 | CHHEA |      |    0 |
| SSA          | 08700 | CHHEA | 2XL  |    6 |
| SSA          | 08700 | CHHEA | 3XL  |    7 |

MPRODUCT（主产品列表）


| Manufacturer | Style | Color | Size | Sort |
|--------------|-------|-------|------|------|
| LIB          | 586M  | FL    | XS   |    1 |
| LIB          | 586M  | FL    | S    |    2 |
| LIB          | 586M  | FL    | M    |    3 |
| LIB          | 586M  | FL    | L    |    4 |
| LIB          | 586M  | FL    | XL   |    5 |
| LIB          | 586M  | FL    | 2XL  |    6 |
| LIB          | 586M  | FL    | 3XL  |    7 |
| LIB          | 586M  | FL    | 4XL  |    8 |
| LIB          | 586M  | FL    | 6XL  |   10 |
| SSA          | 08700 | CHHEA | S    |    2 |
| SSA          | 08700 | CHHEA | M    |    3 |
| SSA          | 08700 | CHHEA | L    |    4 |
| SSA          | 08700 | CHHEA | XL   |    5 |
| SSA          | 08700 | CHHEA | 2XL  |    6 |
| SSA          | 08700 | CHHEA | 3XL  |    7 |

因此，我使用 WHERE NOT EXISTS 子句从 MPRODUCT 表中选择 ContractItems 表中不存在的尺寸。这是我正在运行的查询：

SELECT * FROM

    (SELECT

         ci.MANUFACTURER                [Vendor],
         ci.Style                       [Style],
         ci.Color                       [Color],
         ci.Item_Size                   [Size],
         mp.sort                        [Sort]

    from dbo.contractitems ci

    join MPRODUCT mp on mp.BARCODE = ci.barcode

    WHERE ci.Item_Size <> ''
    and con_num = 1068
    and con_store = 98
    and item_type = 0
    and ((mp.MANUFACTURER = 'LIB' and mp.STYLE = '586M') 
    or (mp.MANUFACTURER = 'SSA' and mp.STYLE = '08700'))

    UNION ALL

    SELECT

         mp.MANUFACTURER                [Vendor],
         mp.Style                       [Style],
         mp.Color                       [Color],
         mp.Item_Size                   [Size],
         mp.sort                        [Sort]


    FROM MPRODUCT MP

    join ContractItems as ci on

        CI.MANUFACTURER = MP.MANUFACTURER
    and CI.STYLE = MP.STYLE
    and CI.COLOR = MP.COLOR

    WHERE NOT EXISTS
      (SELECT 1
       from dbo.contractitems ci
       WHERE ci.MANUFACTURER = mp.MANUFACTURER
            and ci.Style = mp.STYLE
            and ci.Color = mp.COLOR
            and ci.item_size = mp.item_size)

    and con_num = 1068
    and con_store = 98
    and item_type = 0
    and ((mp.MANUFACTURER = 'LIB' and mp.STYLE = '586M') 
    or (mp.MANUFACTURER = 'SSA' and mp.STYLE = '08700'))
    and ci.ITEM_SIZE = '')a

ORDER BY Vendor, Style, Color, Sort

这适用于 SSA-08700，但不适用于 LIB-586M。这是我的结果集。

结果：

| Manufacturer | Style | Color | Size | Sort |
|--------------|-------|-------|------|------|
| LIB          | 586M  | FL    | XS   |    1 |
| LIB          | 586M  | FL    | 2XL  |    6 |
| LIB          | 586M  | FL    | 3XL  |    7 |
| SSA          | 08700 | CHHEA | S    |    2 |
| SSA          | 08700 | CHHEA | M    |    3 |
| SSA          | 08700 | CHHEA | L    |    4 |
| SSA          | 08700 | CHHEA | XL   |    5 |
| SSA          | 08700 | CHHEA | 2XL  |    6 |
| SSA          | 08700 | CHHEA | 3XL  |    7 |

预期结果：将模仿 MPRODUCT 表，但从 ContractItems 表中提取未见过的价格。

| Manufacturer | Style | Color | Size | Sort |
|--------------|-------|-------|------|------|
| LIB          | 586M  | FL    | XS   |    1 |
| LIB          | 586M  | FL    | S    |    2 |
| LIB          | 586M  | FL    | M    |    3 |
| LIB          | 586M  | FL    | L    |    4 |
| LIB          | 586M  | FL    | XL   |    5 |
| LIB          | 586M  | FL    | 2XL  |    6 |
| LIB          | 586M  | FL    | 3XL  |    7 |
| LIB          | 586M  | FL    | 4XL  |    8 |
| LIB          | 586M  | FL    | 6XL  |   10 |
| SSA          | 08700 | CHHEA | S    |    2 |
| SSA          | 08700 | CHHEA | M    |    3 |
| SSA          | 08700 | CHHEA | L    |    4 |
| SSA          | 08700 | CHHEA | XL   |    5 |
| SSA          | 08700 | CHHEA | 2XL  |    6 |
| SSA          | 08700 | CHHEA | 3XL  |    7 |

有没有人知道为什么它只会为 LIB-586M 选择 XS 记录而为其他人拉所有尺寸？

联盟之前的结果：

| Manufacturer | Style | Color | Size | Sort |
|--------------|-------|-------|------|------|
| LIB          | 586M  | FL    | 2XL  |    6 |
| LIB          | 586M  | FL    | 3XL  |    7 |
| SSA          | 08700 | CHHEA | 2XL  |    6 |
| SSA          | 08700 | CHHEA | 3XL  |    7 |

联盟后的结果：

| Manufacturer | Style | Color | Size | Sort |
|--------------|-------|-------|------|------|
| LIB          | 586M  | FL    | XS   |    1 |
| SSA          | 08700 | CHHEA | S    |    2 |
| SSA          | 08700 | CHHEA | M    |    3 |
| SSA          | 08700 | CHHEA | L    |    4 |
| SSA          | 08700 | CHHEA | XL   |    5 |

标签： sqlsql-serverdatabase

解决方案

解决了。我不知道为什么这会奏效，但我在 WHERE NOT EXISTS 子句中添加了更多子句，它似乎可以解决问题。

这是工作代码：

SELECT * FROM

    (SELECT

        dbo.trim(ci.MANUFACTURER)      [Vendor],
        dbo.trim(ci.Style)             [Style],
        dbo.trim(ci.Color)             [Color],
        dbo.trim(ci.Item_Size)         [Size],
        ci.sort                        [Sort]

    from dbo.contractitems ci

    WHERE ci.Item_Size <> ''
    and ci.status = 0
    and con_num = 1068
    and con_store = 98

    UNION ALL

    SELECT

        MP.MANUFACTURER                [Vendor],
        MP.Style                       [Style],
        MP.Color                       [Color],
        MP.Item_Size                   [Size],
        Mp.SORT                        [Sort]

    FROM MPRODUCT MP

    join CITEM as ci on CI.MANUFACTURER = MP.MANUFACTURER and CI.STYLE = MP.STYLE and CI.COLOR = MP.COLOR

    WHERE NOT EXISTS
        ( select 1 from ContractItems ci

        where
                con_num = 1068
            and CON_STORE = 98
            and ci.status = 0
            and CI.MANUFACTURER = MP.MANUFACTURER
            AND CI.STYLE = MP.STYLE
            AND CI.ITEM_SIZE = MP.ITEM_SIZE
            AND CI.COLOR = MP.COLOR)

    and con_num = 1068
    and con_store = 98
    and item_type = 0
    and ci.item_size = ''
    and ci.STATUS = 0)a

ORDER BY Vendor, Style, Color, Sort

推荐阅读