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

Question

我正在使用 MySql 5.6 并且我有 2 个表(将它们简化以将列的 nb 减少到对这个问题很重要的部分)和一个今天使用 LEFT JOIN 来选择行的查询。

这是测试您的解决方案的 fiddle :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 的所有 query_results和给定的 customer_id = 12和其他一些小限制(例如 datecol > 30)。

  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_nb

• 由给定的 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              |                
+-------------------+-----------------+--------------+-----------------+------------

ma​​x_email_nb 中的值不正确:根据我的目标，我希望 max_email_nb 的值等于 1而不是 9 .我期望的输出是:

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

确实我想为每个 query_result 检索这是我的 SQL 查询输出的，由 given Customer_id 12 发送的最高 email_nb在 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 !

这里有一个 fiddle 来测试你的解决方案:https://www.db-fiddle.com/f/nGVFhr3xMwKk9CDw6N6FWc/13

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

Answer 1

我在 fiddle 上试过这个，效果很好，

你也可以试试这个。

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
    and qr.query_result_id = coe.query_result_id
  WHERE 
    qr.query_id = 2 AND
    qr.datecol >= 30 AND
    qr.author IS NOT NULL            
      AND qr.query_result_id NOT IN (
        SELECT query_result_id
        FROM customers_emails
        WHERE 
          (
            customer_id = 12 AND
            email_nb = 3
          )
      )
  GROUP BY
    qr.author
  ORDER BY 
    qr.query_result_id ASC
  LIMIT 
    20