我使用MySQL 5.6和我有2个表（简化他们减少列的NB对于这个问题，什么事情），与今天是一个LEFT JOIN来选择行使用查询。
表“query_results”
+-----------------------------+------------+--------------+
| query_result_id | query_id | author |
+-----------------------------+------------+--------------+
| 100 | 1 | john |
| 101 | 1 | eric |
| 102 | 2 | emily |
| 103 | 2 | emily |
| 104 | 3 | john |
+-----------------------------+------------+--------------+
表“customers_emails”
+-------------------+-----------------+--------------+-----------+-------------+------------------------
| customer_email_id | query_result_id | customer_id | author | email_nb | ... days_since_sending
+-------------------+-----------------+--------------+-----------+-------------+------------------------
| 5 | 102 | 12 | emily | 0 | ...
| 12 | 102 | 12 | emily | 1 | ...
| 13 | 103 | 7 | emily | 2 | ...
+-------------------+-----------------+--------------+-----------+-------------+-----------------------
我的当前查询用于获取（在本实施例2）的给定query_id所有query_results和（在该实施例12）在给定的customer_id和其他一些约束。
SELECT
qr.query_result_id,
qr.query_id,
qr.url,
qr.title,
qr.article_publication_day,
qr.media_name,
qr.author_full_name,
qr.author_first_name,
qr.author_last_name,
qr.author,
MAX(coe.email_nb) as max_email_nb
FROM
query_results qr
LEFT JOIN
customers_emails coe
ON
qr.author = coe.author
WHERE
qr.query_id = 2 AND
qr.article_publication_day >= CURDATE() - INTERVAL 30 DAY AND
qr.author IS NOT NULL
AND qr.author NOT IN (
SELECT author
FROM customers_emails
WHERE
(
customer_id = 12 AND
email_nb = 3
)
)
GROUP BY
qr.author
ORDER BY
qr.query_result_id ASC
LIMIT
20
当我创建别名max_email_nb你必须明白这意味着什么： 这意味着最高email_nb通过这个客户发送给笔者对这个查询
今天，我的输出是：
+-------------------+-----------------+--------------+-----------------+-------------
| query_result_id | query_id | author | max_email_nb | ...
+-------------------+-----------------+--------------+-----------------+------------
| 102 | 2 | emily | 2 |
+-------------------+-----------------+--------------+-----------------+------------
但是我需要：
+-------------------+-----------------+--------------+-----------------+-------------
| query_result_id | query_id | author | max_email_nb | ...
+-------------------+-----------------+--------------+-----------------+------------
| 102 | 2 | emily | 1 |
+-------------------+-----------------+--------------+-----------------+------------
事实上，我想检索每个query_result由我的SQL查询，最高email_nb称为max_email_nb等于最高输出email_nb ALL的customers_emails由给定的客户与给定发query_id这个author 。
这意味着上述结果，我应该有max_email_nb = 1而不是（从customers_email第二排来） 2 ，因为它是今天，因为这个author = emily和query_id =2和customer_id = 2 ，最高数（ 不包括其他任何的约束 ）为email_nb是1 。
我试着子查询，我试过内部联接......但没有任何工程。
