我正在嘗試讓 MySQL 查詢作業，以查看今天是否是客戶的第一個訂單日期。我正在查看單個資料庫表 - tblOrders
到目前為止，我的嘗試導致回傳的訂單恰好落在今天的某個日期，但不是他們的“第一”訂單。
SELECT FirstName, LastName, Email, COUNT(OrderID) AS NumberOfOrders, CreateDate
FROM tblOrders
WHERE (MONTH(CreateDate) = MONTH(NOW())
AND DAY(CreateDate) = DAY(NOW()))
GROUP BY Email
ORDER BY CreateDate
我覺得我需要一個子查詢，但我正在轉動我的輪子。
uj5u.com熱心網友回復：
如果您想要客戶的第一個訂單，則需要使用MIN(CreateDate)，但聚合不能出現在WHERE它們必須出現在 中HAVING，因此您的查詢將是：
SELECT o.FirstName,
o.LastName,
o.Email,
COUNT(o.OrderID) AS NumberOfOrders,
MIN(o.CreateDate) AS FirstOrder
FROM tblOrders AS o
GROUP BY o.FirstName, o.LastName, o.Email
HAVING MONTH(MIN(o.CreateDate)) = MONTH(NOW())
AND DAY(MIN(o.CreateDate)) = DAY(NOW());
但是，您的問題是，您的計數將僅計算與今天相同的月份/日期的訂單，而不是該客戶的所有訂單，因此，如果您只想知道客戶的詳細資訊，這很好，但是如果您需要該客戶的訂單總數您需要一個子查詢（如您所想）：
SELECT o.FirstName, o.LastName, o.Email, o.NumberOfOrders, o.FirstOrder
FROM (
SELECT o.FirstName,
o.LastName,
o.Email,
MIN(o.CreateDate) AS FirstOrder,
COUNT(*) AS NumberOfOrders
FROM tblOrders AS o
GROUP BY o.FirstName, o.LastName, o.Email
) AS o
WHERE MONTH(o.FirstOrder) = MONTH(NOW())
AND DAY(o.FirstOrder) = DAY(NOW());
uj5u.com熱心網友回復：
WITH cte AS (
SELECT *, MIN(CreateDate) OVER (PARTITION BY Email) CreateDate
FROM tblOrders
)
SELECT FirstName, LastName, Email, COUNT(DISTINCT OrderID) AS NumberOfOrders, CreateDate
FROM cte
WHERE DATE_FORMAT(CreateDate, '%m%d') = DATE_FORMAT(CURRENT_DATE, '%m%d')
GROUP BY FirstName, LastName, Email, CreateDate
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/484573.html