mysql - WHERE 子句中的查询错误

Question

SELECT product_key.contact_email, product_key.client_name, product_key.status, product_key.key, payment.paymentdate, product_key.id, MAX(paymentdate) AS latest_payment, DATE_ADD(MAX(paymentdate), INTERVAL 1 MONTH) AS expiration_date 
FROM product_key LEFT OUTER JOIN payment ON payment.keyid=product_key.id 
WHERE product_key.status = 'purchased' AND expiration_date = DATE_ADD(NOW(), INTERVAL 10 DAY) GROUP BY product_key.id 
ORDER BY client_name asc

这是我的查询。我知道我不能在 WHERE 子句中使用别名，因为 WHERE 在 SELECT 之前首先被读取。但即使我使用这样的东西:

SELECT product_key.client_name, DATE_ADD(MAX(paymentdate), INTERVAL 1 MONTH) AS expiration_date 
FROM product_key LEFT OUTER JOIN payment ON payment.keyid=product_key.id 
WHERE DATE_ADD(MAX(paymentdate), INTERVAL 1 MONTH) = DATE_ADD(NOW(), INTERVAL 10 DAY) AND product_key.status = 'purchased' 
GROUP BY product_key.id 
ORDER BY client_name asc

还是有错误。请帮忙。谢谢。

Answer 1

由于您使用的是聚合 MAX()，因此您应该将其放在 HAVING 子句而不是 WHERE 中。假设您的查询除此之外是正确且有效的，您可以像这样重写它

SELECT product_key.client_name, 
       DATE_ADD(MAX(paymentdate), INTERVAL 1 MONTH) AS expiration_date 
  FROM product_key LEFT OUTER JOIN payment 
    ON payment.keyid=product_key.id 
   AND product_key.status = 'purchased' 
 GROUP BY product_key.id 
HAVING DATE_ADD(MAX(paymentdate), INTERVAL 1 MONTH) = DATE_ADD(NOW(), INTERVAL 10 DAY) 
 ORDER BY client_name

或

SELECT product_key.client_name, 
       DATE_ADD(MAX(paymentdate), INTERVAL 1 MONTH) AS expiration_date 
  FROM product_key LEFT OUTER JOIN payment 
    ON payment.keyid=product_key.id 
   AND product_key.status = 'purchased' 
 GROUP BY product_key.id 
HAVING expiration_date = DATE_ADD(NOW(), INTERVAL 10 DAY) 
 ORDER BY client_name