如何在没有子查询的情况下在 SQL 中进行多个成员资格测试？

Question

我确信这是一个非常简单的 SQL 问题，但恐怕我的 SQL 能力还达不到我能够弄清楚的水平。

我有一张销售订单表和一张客户表。每个销售订单都有一个为其开票客户的 ID，以及一个为其发货客户的 ID（它们可能相同，但大多数情况下不同 - 之间存在一对多关系） “收单至”和“发货至”[例如，假设我要向 Google 出售某些东西，“收单至”将是 Google 的山景城总部，但“发货至”可能是 Google 的日内瓦办事处。）

所以我'有一个查询基本上询问以下问题： “请提供销售订单表中的总销售额，其中该客户的收单方与 John Doe 关联，或者该客户的收货方与 John Doe 关联，但也没有收单方。”

我的查询相当简单（读：天真）

    SELECT SUM(price) FROM salesOrders
    WHERE
    (
    ( salesOrders.BillTo IN (SELECT customerID FROM customers WHERE customers.salesman = 'JOHNDOE')

    OR

    (salesOrders.ShipTo IN (SELECT customerID FROM customers WHERE customers.salesman = 'JOHNDOE') 
AND 
salesOrders.BillTo NOT IN (SELECT customerID FROM customers WHERE customers.salesman = 'JOHNDOE')
    )
    )

查询有效，但它需要太长的时间（执行约 6 秒）。我几乎肯定有一种非常简单的方法可以提取子查询，这样我就不必将其重复三次，或者完全删除子查询并用联接替换它们，我只是不知道该怎么做。 :( 从好的方面来说，这对于其他人来说可能很容易:)

提前致谢。

Answer 1

SELECT SUM(so.price) 
    FROM salesOrders so
        LEFT JOIN customers c1
            on so.BillTo = c1.customerID
                and c1.salesman = 'JOHNDOE'
        LEFT JOIN customers c2
            on so.ShipTo = c2.customerID
                and c2.salesman = 'JOHNDOE'
    WHERE c1.customerID IS NOT NULL
        OR (c2.customerID IS NOT NULL AND c1.customerID IS NULL)

Answer 2

SELECT sum(s.price)
FROM salesOrders s
  inner join customers bt on s.billto = bt.customerID
  inner join customers st on s.ShipTo = st.customerID
WHERE
  s.billto <> s.shipto and 'JONHDOE' in (bt.salesman, st.salesman);

Answer 3

我不知道您使用的是什么数据库引擎，但如果它功能强大，将允许您创建临时/变量表。

我相信您已经注意到，您使用了相同的 select 3 次，如果执行一次，将结果保存到临时表并在使用后将其删除，可能会更有用。

Answer 4

最能提高性能的是拥有正确的索引。
你不会说你运行的是哪个 SQL 服务器，如果它是 SQL 2008，它会给你在查看查询计划时添加索引的提示。

我更喜欢使用存在，据我所知，使用它并没有太大的性能提升。您应该查看查询计划进行检查。

 SELECT SUM(price) FROM salesOrders
            WHERE
            EXISTS (SELECT * FROM customers WHERE 
                                customers.salesman = 'JOHNDOE'
                                and ( (salesOrders.ShipTo = customers.CustomerId  
                                        and not salesOrders.BillTo = customers.CustomerId)                  
                                or salesOrders.BillTo = customers.CustomerId
                             ) ) 

Answer 5

假设您在 salesOrders 中有一个 id 列（此处简称为 ID）：

SELECT SUM(s.Price)
FROM (
  SELECT ID, Price, ShipTo AS CustomerID FROM salesOrders
  UNION
  SELECT ID, Price, BillTo FROM salesOrders
) s
  INNER JOIN customers c ON s.CustomerID = c.CustomerID
WHERE c.salesman = 'JOHNDOE'

UNION 消除了重复项，因此 BillTo 的情况code> 与 ShipTo 相同，不会计算两次。