MySQL - 检索 LEFT JOIN 中关联列的最大值，其周长与主查询的 WHERE 子句不同

Question

我正在使用 MySql 5.6 并有一个带有 LEFT JOIN 的选择查询，但我需要检索关联列的最大值 email_nb ) 但具有不同的“周长”约束。
让我们举个例子:让我声明它只是一个只有 5 行的例子，但是当我有数千行时它也应该工作......(我之所以这么说是因为我的查询中有一个 LIMIT 子句)
表“查询结果”

+-----------------------------+------------+--------------+
| query_result_id             | query_id   | author       |
+-----------------------------+------------+--------------+
| 2                           |         1  | john         |
| 3                           |         1  | eric         |
| 7                           |         3  | martha       |
| 9                           |         4  | john         |
| 10                          |         1  | john         |
+-----------------------------+------------+--------------+

表'customers_emails'

+-------------------+-----------------+--------------+-----------+-------------+------------------------
| customer_email_id | query_result_id | customer_id  | author    |  email_nb   | days_since_sending
+-------------------+-----------------+--------------+-----------+-------------+------------------------
| 5                 |         2       | 12           |  john     |   2         |  150
| 12                |         3       | 7            |  eric     |   4         |  90
| 27                |         3       | 12           |  eric     |   2         |  86
| 40                |         9       | 15           |  john     |   9         |  87
| 42                |         2       | 12           |  john     |   7         |  23
| 51                |         10      | 12           |  john     |   3         |  89
+-------------------+-----------------+--------------+-----------+-------------+-----------------------

备注:

你可以有一个query_result作者在任何 customers_emails 中都没有出现在任何行中，因此 LEFT JOIN我正在使用。

你可以看到author设计上是重复的，因为它在第一个表和第二个表上每次都与 query_result_id 相关联.重要的是要注意。

email_nb是 0 到 10 之间的整数

有一个LIMIT子句，因为我需要检索一组记录

今天我的查询旨在检索 query_results具有一定数量的条件 特殊性是我确保使用 author 检索 query_results谁没有出现在任何 customer_email_id days_since_sending将少于 60 天:这意味着我检查了这些 days_since_sending不仅在此查询的记录中，而且在 中全部customers_emails 感谢子查询 NOT IN (见下文)。
这是 我当前的查询 对于 customer_id = 12和 query_id = 1

  SELECT             
    qr.query_result_id,     
    qr.author,
  FROM
    query_results qr
  LEFT JOIN
    customers_emails ce
  ON
    qr.author = ce.author           
  WHERE
    qr.query_id = 1 AND
    qr.author IS NOT NULL            
      AND qr.author NOT IN (
        SELECT recipient
        FROM customers_emails
        WHERE               
          (
            customer_id = 12 AND
            ( days_since_sending >= 60) ) 
          )           
      )     
  # we don't take by coincidence/bad luck 2 query results with the same author
  GROUP BY
    qr.author
  ORDER BY 
    qr.query_result_id ASC
  LIMIT 
    20

这是预期的输出:

+-----------------------------+------------+--------------+
| query_result_id             | author     | email_nb     |  
+-----------------------------+------------+--------------+
|        10                   |   john     |    7         |
|        3                    |   eric     |    2         |   
+-----------------------------+------------+--------------+

我今天的挑战/困难:

第 2 行的通知 Eric 绑定(bind)到 email_nb 2而不是 Eric 的所有电子邮件中的最大值，可能是 4如果我们取了 email_nb 的最大值跨越所有发往 author=eric 的消息.但我们保持在 customer_id = 12 的限制范围内所以只剩下一个 email_nb = 2

另请注意，在第一行，email_nb与 query_result = 10 相关联是 7 , 而不是 3 , 可能是 3是表中显示的内容customers_emails在最后一行。

事实上，对于给“约翰”的电子邮件，我可以在 email_nb 之间进行选择。 2 , 7和 3但我取最高，所以它是 7 (即使这封电子邮件来自 60 多天前!这非常重要，也是我不知道该怎么做的一部分:周长不同:今天我检索了 author 未出现的所有查询结果在过去 60 天内发送了一封电子邮件(请参阅 NOT IN 子查询)但我需要在列中包含 email_nb 和 john 发送到 customer_id=12 的最大值 query_id=1，即使发送超过 60几天前所以这些是不同的周长......真的不知道该怎么做......

换句话说，我不想在同一个 WHERE 中找到最大值 (email_nb) days_since_sending >= 60 等子句或在相同的 LIMIT 和 GROUP BY 内...作为我当前的查询:我需要的是检索 email_nb 的最大值对于 customer_id=12和 query_id=1并发送至john横跨 customers_emails 上的所有记录 table !

如果customers_emails 上根本没有关联的行(这意味着该客户过去没有为此查询发送过任何电子邮件)，那么email_nb 应该是NULL。

这意味着我不想要这个输出:

+-----------------------------+------------+--------------+
| query_result_id             | author     | email_nb     |  
+-----------------------------+------------+--------------+
|        10                   |   john     |    3         |
|        3                    |   eric     |    2         |   
+-----------------------------+------------+--------------+

如何在 MySQL 5.6 中实现这一点？

Answer 1

由于你有点困惑，我想出了这个。

select 
max(q.query_result_id) as query_result_id,q.author,max(email_nb) as email_nb
from query_results q
left join customers_emails c on q.author=c.author
where customer_id=12 and query_id=1
group by q.author;