SQL Server-在何处和选择中使用Exists子句

我有这样的看法

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)

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

我们正在添加第二种类型的订单项，并且我已经修改了 View ，使其包含至少一个类型为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))

这很容易，但是我刚刚添加了一个要求，以显示订单在显示这些结果的网格中是否包含类型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))

但这与EXISTS子句的重复有点不对劲。有更好的qway可以编写它吗？我可以使其性能更好吗？

最佳答案

您可以在OrderLineItemType1和OrderLineItemType2上保留JOIN的权限，然后在WHERE子句中过滤掉那些两列均为NULL的行。

关于SQL Server-在何处和选择中使用Exists子句，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/4655981/