时间戳

首页 > 解决方案 > 嵌套选择不为空的 SQL

sql - 嵌套选择不为空的 SQL

问题描述

我有一张Customers桌子CustomerID和CustomerName。

然后我有一张Orders桌子，有CustomerID,datetime OrderPlaced和datetime OrderDelivered。

请记住，并非所有客户都已下订单，我想获取 的列表CustomerName，但仅针对已下订单且已交付订单的客户，并且仅针对每位客户的最新OrderPlaced订单。OrderDeliveredOrderPlaced

我开始做（完全意识到这还没有实现OrderDelivered对它的限制，但已经没有做我想要的）：

SELECT CustomerID,
    (SELECT TOP 1 OrderDelivered 
        FROM Orders ORDER BY OrderDelivered DESC) AS OrderDelivered
FROM Customer
WHERE OrderDelivered IS NOT NULL

但是 MS SQL 已经不喜欢这样了，它说它不知道子句OrderDelivered上有什么。WHERE

我怎样才能做到这一点？

标签： sqlsql-server

解决方案

Personally, I would move your subquery into the FROM and use CROSS APPLY. Then you can far more easily reference the column:

SELECT C.CustomerID,
       O.OrderDelivered
FROM Customer C
     CROSS APPLY (SELECT TOP 1 OrderDelivered 
                  FROM Orders oa
                  WHERE oa.CustomerID = C.CustomerID --Guess column name for orders
                    AND O.OrderDelivered IS NOT NULL
                  ORDER BY O.OrderDelivered DESC) O;

As, however, this is a CROSS APPLY, then the results will already be filtered; so no need for the WHERE.

推荐阅读