主页 / dba / 问题 / 283514
Accepted
Joe Pineda
Asked: 2021-01-19 12:31:31 +0800 CST

不存在与主查询中的 2 个字段匹配的两个子查询字段

  • 772

背景：文档管理系统中有两种不同的文档类型。Doc Type A 和 Doc Type B 都有一个 Ticket # 和一个 Ticket Date。我们正在寻找：Doc Type A 文档没有匹配的 Doc Type B 文档（不存在）且 Ticket # 和 Ticket Date 相同。类似的 Doc Type B 文档具有相同的 Ticket # 但不同的 Ticket Date。我们想忽略这些。看起来很简单......但我被卡住了。到目前为止，我所拥有的是这样的：

    SELECT 
       DISTINCT ki110.keyvaluechar AS "Ticket #",
       ki101.keyvaluedate AS "Ticket Date"
    FROM 
       itemdata 
    LEFT OUTER JOIN
       hsi.keyitem110 ki110 on ( itemdata.itemnum = ki110.itemnum )
    LEFT OUTER JOIN 
       hsi.keyitem101 ki101 on ( itemdata.itemnum = ki101.itemnum )
    WHERE  
       ki101.keyvaluedate BETWEEN '01-01-2021' AND '01-31-2021'
    AND 
      ( 
        itemdata.itemtypenum  = 178  
      ) -- this is Doc Type A
    AND NOT EXISTS
       (
        SELECT
           ki110.keyvaluechar, 
           ki101.keyvaluedate
        FROM
           itemdata, 
           keyitem110 ki110 , 
           keyitem101 ki101
        WHERE  --(itemdata.itemnum = ki110.itemnum) --Ticket #
   
                -- ** the problem is here  for Date: I need to say Date in Doc Type B doc is not the same as Date in Doc Type A doc using ki101.keyvaluedate)

        AND itemdata.itemtypenum = 183) -- this  is DOC Type B
sql-server exists

3 个回答

  1. Best Answer

    你没有EXISTS正确使用。在 anEXISTS中，所选列没有区别，它被完全忽略，甚至不需要名称。这就是为什么我喜欢将语法EXISTS (SELECT 1全部放在一行上的原因，因为实际上它只是EXISTS子查询的 not 的额外语法。

    在您的示例中，您还需要将子查询与外部相关联。

    编辑我想我知道你现在想要什么。

    如果您遵循逻辑，则需要排除外部中的行，其中内部中存在具有相同票证和日期的匹配行。

    笔记：

    1. 在这里LEFT JOIN没有意义，因为您正在过滤它的列，它也可能是一个INNER JOIN.
    2. 日期的最佳格式是yyyy-mm-dd不会因语言和地区而改变。
    3. 您应该使用datetime带有时间分量的半开区间，否则您会得到错误的结果，所以dateCol >= start and dateCol < dayAfterEnd. 我没有改变这一点，因为你可能有一个date-only 列。
    4. 为别名使用更合理的名称
    5. 我们可以将连接逻辑提取到 CTE 中
    6. 不要使用需要引用的列名
    ;WITH cte AS (
    SELECT
       ticketNumber.keyvaluechar AS TicketNum,
       ticketDate.keyvaluedate AS TicketDate,
       itemdata.itemtypenum,
       itemdata.itemnum
    FROM 
       itemdata 
    INNER JOIN
       hsi.keyitem110 ticketNumber on ( itemdata.itemnum = ticketNumber.itemnum )
    INNER JOIN 
       hsi.keyitem101 ticketDate on ( itemdata.itemnum = ki101.itemnum )
)
SELECT DISTINCT
   TicketNum, TicketDate
FROM 
   cte
WHERE  
   ticketDate BETWEEN '01-01-2021' AND '01-31-2021'
AND 
  ( 
    cte.itemtypenum  = 178  
  ) -- this is Doc Type A
AND NOT EXISTS (SELECT 1
    FROM
       cte cte2
    WHERE cte2.itemnum = cte.itemnum --Same Ticket #
        AND cte2.TicketDate = cte.TicketDate  -- Same dates
        AND cte2.itemtypenum = 183  -- this  is DOC Type B
)

    另请注意，根据此，您LEFT JOIN的 s 可能应该是INNER JOIN

    正如我看到有人提到的那样NOT IN，我只会说：NOT IN通常是一个非常糟糕的主意，因为在可为空的列上它会提供不正确的数据。

    例如，请参阅此处以了解原因。

    • 4

  2. --Doc Type A 文档没有匹配的 Doc Type B doc (NOT EXISTS) 且 Ticket # 和 Ticket Date 相同

    选择
      *
    从
    keyitem101 ki1011 在哪里
       不存在
       （选择 * 从
       keyitem101 ki101
        加入keyitem110 ki110
    在 ki101.itemnum = ki110.itemnum 和 ki101.keyvaluedate = ki110.keyvaluedate
    其中 ki1011.itemnum =ki101.itemnum 和 ki1011.keyvaluedate=ki101.keyvaluedate)
    和转换（VARCHAR（10），ki1011.keyvaluedate，101）在'01/01/2021'和'01/31/2021'之间;

    -- 有类似的文档类型 B 文档具有相同的票号但不同的票证日期我们想忽略这些。

       选择
      *
    从
    keyitem110 ki1101 在哪里
       不存在
       （选择 * 从
       keyitem110 ki110
        加入keyitem101 ki101
    在 ki110.itemnum = ki101.itemnum 和 ki110.keyvaluedate ki101.keyvaluedate
    其中 ki1101.itemnum =ki110.itemnum 和 ki1101.keyvaluedate=ki110.keyvaluedate)

    基于这样的数据模型

       

创建表itemdata（itemnum numeric,itemtypenum numeric）；
创建表 keyitem110 (itemnum numeric,keyvaluechar varchar(10),keyvaluedate date,itemtypenum numeric);
创建表 keyitem101 (itemnum numeric,keyvaluechar varchar(10),keyvaluedate date,itemtypenum numeric);

插入项目数据值（1,183），（2,178），（2,183），（3,183），（4,178），（4,183），（5,183），（6,178），（6,183），（7,183），（8,178）；
插入 keyitem101 值 (6,'docA','01-17-2021',178),(4,'docA','01-19-2021',178),(4,'docA', '09- 12-2020',178),(2,'docA','01-10-2021',178),
                (8,'docA','12-01-2021',178),(4,'docA','01-12-2020',178)

插入 keyitem110 值 (1,'docB','11-15-2020',183),(1,'docB','01-16-2021',183),(2,'docB','01- 11-2021',183),(3,'docB','10-10-2020',183),(3,'docB','01-10-2021',183),
                                (4,'docB', '01-19-2021',183),(5,'docB', '11-15-2021',183),(5,'docB','01-19-2021' ,183),(6,'docB','12-17-2020',183),(6,'docB', '01-17-2021',183),
                               (7,'docB', '01-15-2020',183),(7,'docB','01-10-2021',183);
    • 0

  3. 左连接怎么样？伪代码

    SELECT ...
   FROM itemdata i
 LEFT JOIN itemdata i2
   ON i.ticketNo = i2.ticketNo
  AND i.ticketDate = i2.ticketDate
  AND i2.itemtypenum = 183
WHERE i.itemtypenum = 178
   AND i2.ticketNo is null
    • 0

相关问题