我有两个非常好的查询。而后者对于前者的每一行只运行一次。如果可能的话,我想将它们合并以提高性能。后者返回零或一行。
问题在于这两个查询都使用INNER JOINs,我需要用LEFT JOIN将它们粘合在一起。这是合法的吗?如果在LEFT JOIN之后使用INNER JOIN,这样做的结果是否定义良好?最后,这样的操作是否会对现有的“快速”方法产生任何性能损失?
供参考,如果这太多了,我很抱歉,这是我的第一个查询:
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
;
这个查询的目的是为了找出每个订单是否有顾客在之后重新购买了同款或类似产品。