MySQL-在where子句中引用聚合列 [英] MySQL - Referencing aggregate column in where clause

问题描述

这看起来很简单，但我似乎无法在不进行子查询的情况下弄清楚(这似乎大大降低了查询速度-花费了将近10秒而不是< 1).

This seems so simple but I can't seem to figure it out without doing subqueries (which seem to slow down the queries significantly - takes almost 10 seconds instead of <1).

假设我有一个已发送文档的表，我想选择自上次发送以来已更新的文档和从未发送过的文档.

Let's say I have a table of sent documents, and I want to select the ones that have been updated since they've last been sent, and the ones that have never been sent.

SELECT d.document_id, max(sd.document_sent_date) as last_sent_date
FROM documents d
LEFT JOIN sent_documents sd ON d.document_id=sd.document_id
WHERE last_sent_date is NULL OR last_sent_date<d.last_updated
GROUP BY d.document_id

像这样可能吗?基本上，我想在我的where子句中使用max()的结果.

Is something like this possible? Basically, I want to use the result of max() in my where clause.

推荐答案

您需要having子句.

一般规则:where在聚合之前的日期上操作，having在聚合之后的数据上操作.

General rule: where operates on date before aggregation, having operates on data after aggregation.

这篇关于MySQL-在where子句中引用聚合列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！