mysql - 在MySQL中进行左右联接

我现在有一个查询，如下所示

SELECT
    p.*, total
FROM
    scripts p
LEFT JOIN
    (SELECT script_id, COUNT(*) as total FROM creator_log WHERE created_date > timestampadd(hour, -6, now()) GROUP BY script_id) p2
ON
    p.script_id = p2.script_id
WHERE
    (p2.total < 1 OR p2.total IS NULL)
AND
    p.script_status = '1'
ORDER BY
    p.script_id DESC
LIMIT
    1

然后，如何根据返回的count()从另一个名为script_id的表中找到products并确保p.script_id小于5？
我想我应该做某种右键连接？
编辑我尝试了以下操作，但没有成功

SELECT
    p.*, total, active_total
FROM
    scripts p
LEFT JOIN
    (SELECT script_id, COUNT(*) as total FROM creator_log WHERE created_date > timestampadd(hour, -6, now()) GROUP BY script_id) p2
ON
    p.script_id = p2.script_id
RIGHT JOIN
    (SELECT script_id, COUNT(*) as active_total FROM products WHERE last_used < timestampadd(hour, -6, now()) AND status = 1) p3
ON
    p.script_id = p3.script_id
WHERE
    (p2.total < 1 OR p2.total IS NULL)
AND
    (p3.active_total < 5 or p3.active_total IS NULL)
AND
    p.script_status = '1'
ORDER BY
    p.script_id DESC
LIMIT
    1

最佳答案

不，您需要进行内部连接或完全连接（不是右连接或左连接），如下所示：

SELECT p.*, total
FROM scripts p
LEFT JOIN
    (SELECT script_id, COUNT(*) as total
     FROM creator_log
     WHERE created_date > timestampadd(hour, -6, now())
     GROUP BY script_id) p2
   ON p.script_id = p2.script_id AND p2.total < 1
JOIN
   (SELECT script_id, COUNT(*) as active_total
    FROM products
    WHERE last_used < timestampadd(hour, -6, now()) AND status = 1) p3
  ON p.script_id = p3.script_id AND p3.active_total < 5
WHERE p.script_status = '1'
ORDER BY p.script_id DESC
LIMIT 1

注意，您可以向ON子句添加多个需求，从而使where子句更简单、更易于理解。
这是如何工作的：因为P3上的连接是脚本和P3中的完全连接元素必须存在。这使得p3中的元素如您所描述的那样是“必需的”。

关于mysql - 在MySQL中进行左右联接，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/20390916/