MySQL - 无法在左连接和 2 个不同的选择边界内检索最大值

我正在使用MySql 5.6并且我有 2 个表（简化它们以将 nb 列减少到对这个问题很重要的列），其中包含一个今天使用 LEFT JOIN 来选择行的查询。

这是测试您的解决方案的小提琴：https : //www.db-fiddle.com/f/nGVFhr3xMwKk9CDw6N6FWc/13

表 'query_results'

+-----------------------------+------------+--------------+-----------
| query_result_id             | query_id   | author       |  datecol
+-----------------------------+------------+--------------+-----------
| 100                         |         1  | john         |   80
| 101                         |         1  | eric         |   70
| 102                         |         2  | emily        |   100
| 103                         |         2  | emily        |   100
| 104                         |         4  | emily        |   120
| 105                         |         3  | emily        |   50
+-----------------------------+------------+--------------+-----------

表 'customers_emails'

+-------------------+-----------------+--------------+-----------+-------------+
| customer_email_id | query_result_id | customer_id  | author    |  email_nb 
+-------------------+-----------------+--------------+-----------+-------------+
| 5                 |         758     | 12           |  mathew   |   0  
| 12                |         102     | 12           |  emily    |   0     
| 13                |         103     | 12           |  emily    |   1
| 14                |         104     | 12           |  emily    |   9
| 15                |         102     | 7            |  emily    |   2
+-------------------+-----------------+--------------+-----------+-------------+

我当前的查询成功获取给定query_id = 2和给定customer_id = 12以及其他一些次要约束（例如 datecol > 30）的所有 query_results。

  SELECT            
    qr.query_result_id,
    qr.query_id,
    qr.author
  FROM
    query_results qr
  LEFT JOIN
    customers_emails coe
  ON
    qr.author = coe.author AND
    coe.customer_id = 12           
  WHERE        
    qr.query_id = 2 AND
    qr.datecol >= 30 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 
    2

上面的这个查询完美地工作（我很满意）并给了我：

今天我的输出是：

+-------------------+-----------------+--------------+
| query_result_id   | query_id        | author       | 
+-------------------+-----------------+--------------+
| 102               |         2       | emily        |                 
+-------------------+-----------------+--------------+

现在，我的目标，并在那里我失败是：我只是想一个名为max_email_nb新列添加到当前的输出，将是**“的最高email_nbSENT

• 通过给定customer_id(=12)
• 在这个给定query_id(=2)
• 到author当前 SQL 输出的每一行，在上面的示例中它是emily（但可能有更多行并且无法预测：它来自当前查询！）**

我尝试使用 MAX()：

      SELECT            
        qr.query_result_id,
        qr.query_id,
        qr.author,
        MAX(coe.email_nb) as max_email_nb
      FROM
        query_results qr
      LEFT JOIN
        customers_emails coe
      ON
        qr.author = coe.author AND
        coe.customer_id = 12           
      WHERE        
        qr.query_id = 2 AND
        qr.datecol >= 30 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 
        2

今天我的输出是：

+-------------------+-----------------+--------------+-----------------+-------------
| query_result_id   | query_id        | author       |  max_email_nb   | ... 
+-------------------+-----------------+--------------+-----------------+------------
| 102               |         2       | emily        |  9              |                
+-------------------+-----------------+--------------+-----------------+------------

max_email_nb 中的值不正确：根据我的目标，我希望 max_email_nb 的值等于1而不是9。我期望的输出是：

+-------------------+-----------------+--------------+-----------------+-------------
| query_result_id   | query_id        | author       |  max_email_nb   | ... 
+-------------------+-----------------+--------------+-----------------+------------
| 102               |         2       | emily        |  1              |                
+-------------------+-----------------+--------------+-----------------+------------

事实上，我想检索每个query_result由我的SQL查询，由派出的最高email_nb输出given Customer_id 12上query_id 2这个author（从当前的SQL查询它的未来这个特定的行emily）。

那么这个错误的 9 值是从哪里来的呢？它来自这个输入行：

+-------------------+-----------------+--------------+-----------+-------------+
| customer_email_id | query_result_id | customer_id  | author    |  email_nb   | 
+-------------------+-----------------+--------------+-----------+-------------+
| 14                |         104     | 12           |  emily    |   9  

... 所以它与 相关联query_result_id= 104，它本身在此处定义：

+-----------------------------+------------+--------------+-----------
| query_result_id             | query_id   | author       |  datecol
+-----------------------------+------------+--------------+-----------
| 104                         |         4  | emily        |   120

……原来如此query_id = 4！但正如我在定义我的目标时所说的，我正在寻找与 相关的东西query_id = 2，这就是为什么我不应该只得到9价值1！

这是测试您的解决方案的小提琴：https : //www.db-fiddle.com/f/nGVFhr3xMwKk9CDw6N6FWc/13

我尝试了子查询，我尝试了内部联接……但没有任何效果。