mysql - LEFT JOIN 之后的 INNER JOIN

Question

我有两个非常有效的查询。后者对前者的每一行运行一次。如果可能的话，我想将它们结合起来以提高性能。后者返回零行或一行。

问题是这两个查询都使用了 INNER JOIN，我需要用 LEFT JOIN 将它们粘合起来。这是否合法，是否明确定义了如果在 LEFT JOIN“之后”使用 INNER JOIN 会发生什么。最后，与“快速”的现有方法相比，这样的阴谋会带来任何性能损失吗？

作为引用，如果这是 TMI，我很抱歉，这是我的第一个查询:

SELECT
  pmt_attempts.user_id,
  CONCAT(DATE_FORMAT(attempt_time_end, '%Y') + 2, DATE_FORMAT(attempt_time_end, '-%m-%d')) as date_expired,
  course_sku,
  um_fn.meta_value AS 'shipping_first_name',
  um_ln.meta_value AS 'shipping_last_name',
  um_cr.meta_value AS 'credentials',
  pmt_orders.promocode_used,
  pmt_promocodes.sales_channel
FROM
  pmt_attempts
INNER JOIN
  pmt_courses 
    ON pmt_attempts.course_id = pmt_courses.course_id
INNER JOIN
  pmt_orders 
    ON pmt_orders.order_id = pmt_attempts.order_id
INNER JOIN
  wp_usermeta um_fn 
    ON pmt_orders.user_id = um_fn.user_id
    AND um_fn.meta_key = 'shipping_first_name'
INNER JOIN
  wp_usermeta um_ln 
    ON pmt_orders.user_id = um_ln.user_id
    AND um_ln.meta_key = 'shipping_last_name'
LEFT JOIN
  wp_usermeta um_cr 
    ON pmt_orders.user_id = um_cr.user_id
    AND um_cr.meta_key = 'credentials'
INNER JOIN
  pmt_order_course 
    ON fk_order_id = pmt_attempts.order_id
    AND fk_course_id = pmt_attempts.course_id
LEFT JOIN
  pmt_promocodes 
    ON pmt_orders.promocode_used = pmt_promocodes.promocode_text
WHERE
  attempt_time_end >= '2014-01-01' AND <--- CONFIGURABLE REPORT PARAM
  attempt_time_end < '2014-02-01' AND <--- CONFIGURABLE REPORT PARAM
  attempt_complete = 'Y' AND
  (unenroll_date IS NULL OR unenroll_date = '0000-00-00') AND
  1
ORDER BY
  attempt_time_end
;

这是第二个查询:

SELECT
  course_sku,
  pmt_orders.order_date,
  attempt_time_end,
  pmt_order_course.unenroll_date
FROM
  pmt_order_course
INNER JOIN
  pmt_orders ON 
    pmt_orders.order_id = pmt_order_course.fk_order_id
INNER JOIN
  pmt_courses ON 
    pmt_courses.course_id = pmt_order_course.fk_course_id
LEFT JOIN
  pmt_attempts ON 
    pmt_attempts.course_id = pmt_courses.course_id AND
    pmt_attempts.order_id = pmt_orders.order_id AND 
    pmt_attempts.attempt_complete = 'Y'
WHERE
  pmt_orders.user_id = 928 AND <--- FROM FIRST QUERY ROW
  course_sku LIKE 'BL%' AND <--- FIRST TWO LETTERS OF FIRST QUERY ROW + %
  pmt_orders.order_date > '2012-12-14' <--- FROM FIRST QUERY ROW
ORDER BY
  pmt_orders.order_date
LIMIT
  1
;

此查询的目的是针对每个订单查找客户是否“续订”并在以后购买了另一件相同或相似的产品。

Answer 1

with wloud 的使用在这里很棒，但如果你在 MySQL 中 FROM subquery正是您所需要的。

假设这个长查询是 Q1，并且 course_sku 列来自 pmt_orders 表，我们可以这样写:

SELECT
  course_sku,
  pmt_orders.order_date,
  attempt_time_end,
  pmt_order_course.unenroll_date
FROM
    (Q1 here) q1
INNER JOIN pmt_order_course ON (pmt_orders.user_id = q1.user_id AND pmt_orders.course_sku LIKE CONCAT(q1.course_sku,'%') AND pmt_orders.order_date > q1.date_expired)
INNER JOIN pmt_orders ON (pmt_orders.order_id = pmt_order_course.fk_order_id)
INNER JOIN pmt_courses ON (pmt_courses.course_id = pmt_order_course.fk_course_id)
LEFT JOIN pmt_attempts ON (pmt_attempts.course_id = pmt_courses.course_id AND pmt_attempts.order_id = pmt_orders.order_id AND pmt_attempts.attempt_complete = 'Y')
WHERE 1 = 1      
ORDER BY
  pmt_orders.order_date
LIMIT
  1
;