SQL Server - 在Where和Select中使用Exists子句

I have a view that's something like

我有一个类似的观点

CREATE VIEW OrdersView WITH SCHEMABINDING AS
SELECT o.Id, o.OrderDate, o.LastName, o.TotalPrice, s.Status
FROM dbo.Orders o INNER JOIN dbo.OrderStatus s on o.Id = s.OrderId
WHERE NOT EXISTS (SELECT NULL from dbo.OrderStatus where OrderId = s.OrderId and StatusDate > s.StatusDate
AND EXISTS (SELECT NULL FROM dbo.OrderLineItemType1 WHERE OrderId = o.Id)

The intent is to fetch all orders that have at least one line item of type 1 along with their current status.

目的是获取至少有一个类型1的行项目及其当前状态的所有订单。

We're in the process of adding a second type of line item, and I have modified the view such that it will include orders that have at least one line item of type 1 or type 2:

我们正在添加第二种类型的订单项,并且我修改了视图,使其包含至少包含一个类型1或类型2的订单项的订单:

CREATE VIEW OrdersView WITH SCHEMABINDING AS
SELECT o.Id, o.OrderDate, o.LastName, o.TotalPrice, s.Status
FROM dbo.Orders o INNER JOIN dbo.OrderStatus s on o.Id = s.OrderId
WHERE NOT EXISTS (SELECT NULL from dbo.OrderStatus where OrderId = s.OrderId and StatusDate > s.StatusDate
AND (EXISTS (SELECT NULL FROM dbo.OrderLineItemType1 WHERE OrderId = o.Id)
  OR EXISTS (SELECT NULL FROM dbo.OrderLineItemType2 WHERE OrderId = o.Id))

Easy enough, but I've just had a requirement added to show whether an order contains line items of type 1 or type 2 (or both) in the grid where these results are displayed:

很简单,但我刚刚添加了一个要求,以显示订单是否包含显示这些结果的网格中类型1或类型2(或两者)的行项目:

Order ID | T1 | T2 | Last name | Price    | Status
============================================================
12345    | x  |    | Smith     | $100.00  | In Production
12346    | x  | x  | Jones     | $147.23  | Part Dispatched
12347    |    | x  | Atwood    | $12.50   | Dispatched

The only way I can think of is to do:

我能想到的唯一方法是:

CREATE VIEW OrdersView WITH SCHEMABINDING AS
SELECT o.Id, 
       CASE WHEN EXISTS (SELECT NULL FROM dbo.OrderLineItemType1 WHERE OrderID = o.Id) THEN 1 ELSE 0 END AS HasType1,
       CASE WHEN EXISTS (SELECT NULL FROM dbo.OrderLineItemType2 WHERE OrderId = o.ID) THEN 1 ELSE 0 END AS HasType2,
       o.OrderDate, o.LastName, o.TotalPrice, s.Status
FROM dbo.Orders o INNER JOIN dbo.OrderStatus s on o.Id = s.OrderId
WHERE NOT EXISTS (SELECT NULL from dbo.OrderStatus where OrderId = s.OrderId and StatusDate > s.StatusDate
AND (EXISTS (SELECT NULL FROM dbo.OrderLineItemType1 WHERE OrderId = o.Id)
  OR EXISTS (SELECT NULL FROM dbo.OrderLineItemType2 WHERE OrderId = o.Id))

But this smells wrong with the duplication of the EXISTS clauses. Is there a better qway to write it? Can I make it perform better?

但这与EXISTS条款的重复有些不一致。写一个更好的qway吗?我可以让它表现更好吗?

3 个解决方案

#1

you can LEFT JOIN on OrderLineItemType1 and OrderLineItemType2 and then filter out rows where both of those columns are NULL in the WHERE clause.

您可以在OrderLineItemType1和OrderLineItemType2上LEFT JOIN,然后过滤掉WHERE子句中这两列都为NULL的行。

#2

One change that may be worth profiling (but not directly related to your specific question).

可能值得分析的一个变化(但与您的具体问题没有直接关系)。

The following two lines:

以下两行:

FROM dbo.Orders o INNER JOIN dbo.OrderStatus s on o.Id = s.OrderId
WHERE NOT EXISTS (SELECT NULL from dbo.OrderStatus where OrderId = s.OrderId and StatusDate > s.StatusDate

It may be better to write this as:

写这个可能更好:

FROM dbo.Orders o INNER JOIN dbo.OrderStatus s on o.Id = s.OrderId
        LEFT JOIN dbo.OrderStatus s_later on o.Id  = s_later.OrderId and s_later.StatusDate > s.StatusDate
WHERE s_later.OrderId is null

I usually find that this performs better (but it's one of those that it's worth profiling both ways).

我通常发现这表现得更好(但它是值得两种方式进行分析的其中之一)。

The LEFT JOIN tries to find later rows that apply to the same order, then the WHERE clause rejects any potential result rows where such a match occurred - so the only matching row from s must be the latest one for this order.

LEFT JOIN尝试查找适用于同一订单的后续行,然后WHERE子句拒绝发生此类匹配的任何潜在结果行 - 因此s中唯一匹配的行必须是此订单的最新行。

#3

You don't need EXISTS here at all:

你根本不需要EXISTS:

SELECT  o.Id, HasType1, HasType2, o.OrderDate, o.LastName, o.TotalPrice, s.Status
FROM    dbo.Orders o
CROSS APPLY
        (
        SELECT  TOP 1 s.*
        FROM    dbo.OrderStatus
        WHERE   OrderId = o.Id
        ORDER BY
                StatusDate DESC
        ) s
OUTER APPLY
        (
        SELECT  TOP 1 1 AS HasType1
        FROM    dbo.OrderLineItemType1
        WHERE   OrderID = o.Id
        ) olt1
OUTER APPLY
        (
        SELECT  TOP 1 1 AS HasType2
        FROM    dbo.OrderLineItemType2
        WHERE   OrderID = o.Id
        ) olt2

#1