sql - 更好地查询以获取过去 90 天内没有任何销售的产品列表？

Question

我的代码超级笨拙。你们能否提供一个更好的方法来获得结果

表：产品和订单

SELECT 
    Products.ProductID
FROM 
    Products 
    JOIN Orders
        ON Products.ProductID = Orders.ProductID    
WHERE 
    Products.ProductID NOT IN (
        SELECT 
            Products.ProductID
        FROM 
            Products 
            JOIN Orders
                ON Products.ProductID = Orders.ProductID
        WHERE 
            Orders.OrderDate >= DATEADD(day, -90, GETDATE())
        GROUP BY 
            Products.ProductID
)
GROUP BY 
    Products.ProductID

标签： sqlsql-servertsql

Answer 1

怎么样NOT EXISTS，没有加入？

select p.*
from products p
where not exists (select 1
                  from orders o
                  where o.product_id = p.product_id and
                        o.orderdate >= dateadd(day, -90, getdate())
                 );