我想將我的選擇查詢基于一個條件，所以它類似于：
--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
請問最好的方法是什么？
uj5u.com熱心網友回復：
似乎您可以在 中使用一些條件聚合HAVING來檢查今天是否有訂單：
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;
或者，您也可以使用EXISTS. 我會讓您測驗在您的環境中性能更高的東西。
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;
db<>小提琴
uj5u.com熱心網友回復：
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
uj5u.com熱心網友回復：
另一種方法是使用外部應用來獲取今天的銷售/購買
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
DBFiddle
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/461152.html