SQL语句：根据条件选择数据

Question

我想将我的选择查询基于条件，因此类似于：

--THE LOGIC TO BE IMPLEMENTED
IF ( SELECT OrderPlacementDate from SellOrders WHERE SellOrders.ProductName = Products.ProductName UNION SELECT OrderPlacementDate from BuyOrders WHERE BuyOrders.ProductName = Products.ProductName is today then :

SELECT Products.ProductName, tblCommun.firstdate
FROM Products
OUTER APPLY (SELECT TOP 1 OrderPlacementDate as firstdate
             FROM ( SELECT OrderPlacementDate from SellOrders WHERE SellOrders.ProductName = Products.ProductName UNION SELECT OrderPlacementDate from BuyOrders WHERE BuyOrders.ProductName = Products.ProductName) t 
             ORDER BY OrderPlacementDate ASC) tblCommun;

以下查询给所有产品列表的输出表（其中一些产品中的某些产品今天没有订单 +今天的订单）和日期时间戳为每个产品放置第一订单时：

SELECT Products.ProductName, tblCommun.firstdate
FROM Products
OUTER APPLY (SELECT TOP 1 OrderPlacementDate as firstdate
             FROM ( SELECT OrderPlacementDate from SellOrders WHERE SellOrders.ProductName = Products.ProductName UNION SELECT OrderPlacementDate from BuyOrders WHERE BuyOrders.ProductName = Products.ProductName) t 
             ORDER BY OrderPlacementDate ASC) tblCommun;

ProductName   | firstdate
---------------------------------------
Apple         | 2021-10-12 13:55:50.100
Orange        | 2021-10-15 08:40:14.670
Carrot        | 2021-10-29 08:45:20.110

示例sellorders和buyorders：

sellorders

OrderID  | ProductName  | OrderPlacementDate
-------------------------------------------------
546      | Apple        | 2022-04-20 13:55:30.100
547      | Apple        | 2022-04-20 14:55:50.100
548      | Orange       | 2022-04-20 15:40:50.100
340      | Carrot       | 2022-04-19 13:55:50.100
230      | Carrot       | 2022-04-18 16:50:50.100

buyorders

OrderID  | ProductName  | OrderPlacementDate
-------------------------------------------------
500      | Apple        | 2022-04-20 10:55:30.100
540      | Apple        | 2022-04-20 12:55:50.100
267      | Carrot       | 2022-04-19 10:55:50.100
235      | Carrot       | 2022-04-18 15:50:50.100
229      | Orange       | 2022-04-18 13:40:50.100

目标>目标：我只想显示今天下达订单的产品列表。

今天是2022-04-20，所以我希望拥有的输出表是：

ProductName   | firstdate
---------------------------------------
Apple         | 2021-10-12 13:55:50.100
Orange        | 2021-10-15 08:40:14.670

最佳方法是什么？

Answer 1

似乎您可以在中使用中使用某些条件聚合来检查今天有一个顺序：

WITH Orders AS(
    SELECT SO.ProductID,
           SO.OrderPlacementDate
    FROM dbo.SellOrders SO
    UNION ALL
    SELECT BO.ProductID,
           BO.OrderPlacementDate
    FROM dbo.BuyOrders BO)
SELECT P.ProductName,
       MIN(O.OrderPlacementDate) AS FirstDate
FROM dbo.Product P
     JOIN Orders O ON P.ProductID = O.ProductID
GROUP BY P.ProductName
HAVING COUNT(CASE WHEN O.OrderPlacementDate >= CONVERT(date,GETDATE()) AND O.OrderPlacementDate < DATEADD(DAY,1,CONVERT(date,GETDATE())) THEN 1 END) > 0;

或者，或者，您可以使用存在。我会让您测试在您的环境中更绩效的内容。

WITH Orders AS(
    SELECT SO.ProductID,
           SO.OrderPlacementDate
    FROM dbo.SellOrders SO
    UNION ALL
    SELECT BO.ProductID,
           BO.OrderPlacementDate
    FROM dbo.BuyOrders BO)
SELECT P.ProductName,
       MIN(O.OrderPlacementDate) AS FirstDate
FROM dbo.Product P
     JOIN Orders O ON P.ProductID = O.ProductID
WHERE EXISTS (SELECT 1
              FROM Orders e
              WHERE e.ProductID = O.ProductID
                AND e.OrderPlacementDate >= CONVERT(date,GETDATE())
                AND e.OrderPlacementDate < DATEADD(DAY,1,CONVERT(date,GETDATE())))
GROUP BY P.ProductName;

Answer 2

WITH CTE (
SELECT * FROM SellOrders WHERE CONVERT(date, OrderPlacementDate) = 
CONVERT(date, GETDATE())
UNION
SELECT * FROM BuyOrders WHERE CONVERT(date, OrderPlacementDate) = 
CONVERT(date, GETDATE())
) todaysOrders

SELECT DISTINCT p.ProductNmae, p.firstdate FROM Products p 
INNER JOIN todaysOrders t ON t.ProductName = p.ProductName

Answer 3

另一种方法是使用外部申请来获取今天的卖出/购买

select p.ProductName,
       p.FirstDate
from   Products p
  outer apply (select top 1 s.OrderPlacementDate 
               from SellOrders s 
               where s.ProductName = p.ProductName
               and   convert(date, s.OrderPlacementDate) = convert(date, getdate())
              ) sell
  outer apply (select top 1 b.OrderPlacementDate 
               from BuyOrders b
               where b.ProductName = p.ProductName
               and   convert(date, b.OrderPlacementDate) = convert(date, getdate())
              ) buy
where sell.OrderPlacementDate is not null
or buy.OrderPlacementDate is not null