下面是一种使用多个INNER JOIN子句的方法：

SELECT p.id AS production_id, p.production, s.id AS script_id, s.script
FROM productions p 
INNER JOIN productions_scripts ps ON p.id = ps.production_id
INNER JOIN scripts s ON s.id = ps.script_id
INNER JOIN (
  SELECT ps.production_id
  FROM productions_scripts ps
  INNER JOIN scripts s ON s.id = ps.script_id
  WHERE s.canonical = 1
  GROUP BY ps.production_id
  HAVING COUNT(ps.production_id) > 1
) AS t ON t.production_id = p.id
WHERE s.canonical = 1
ORDER BY p.id;

字符串

有适当的索引肯定会有所帮助，因为这些表是基本的，我只是显示，你可以确认

Table               Index
production          (id, production)
script              (id, canonical, script)
productions_scripts ( production_id, script_id )

字符串
接下来，你的子查询是在目标上的，但是正如其他人所指出的，使用显式连接而不是逗号表列表。
最后，因为这是从MySQL，我会通过在关键字“STRAIGHT_JOIN”，它告诉MySQL做查询的顺序，我已经列出，不要为我想。我不知道你的数据表大小（记录），但从一个系统，我多年前与20+百万记录链接到25+查找表相应的描述，这一个关键字使一个查询从服务器崩溃到在不到2小时的时间内返回结果。
话虽如此，我会修改为：

select STRAIGHT_JOIN
        p.id production_id,
        p.production,
        s.id script_id,
        s.script,
        s.canonical
    from
        (SELECT 
                ps.production_id
            FROM 
                productions_scripts ps
                    JOIN scripts s
                        on ps.script_id = s.id
                        AND s.canonical = 1
            group by
                ps.production_id
            having
                count(*) > 1 ) multi
            JOIN production_scripts ps2
                on multi.production_id = ps2.production_id
                JOIN production p
                    on ps2.production_id = p.id
                JOIN scripts s
                    on ps2.script_id = s.id
    order by
        multi.production_id

型
内部查询依赖于其自身与脚本的连接，以便仅具有任何符合条件的记录的计数> 1。然后，立即将符合条件的生产ID重新连接到其余表以提取相应的详细信息。

可以使用窗口函数代替子查询。

SELECT
  p.production_id,
  p.production,
  p.script_id,
  p.script
FROM (
    SELECT
      p.id AS production_id,
      p.production,
      s.id AS script_id,
      s.script,
      COUNT(*) OVER (PARTITION BY p.id) AS countCanonical
    FROM scripts s
    JOIN productions_scripts ps
      ON s.id = ps.script_id
    JOIN productions p
      ON p.id = ps.production_id
    WHERE s.canonical = 1
) p
WHERE p.countCanonical > 1
ORDER BY production_id;

字符串
请注意，您应该使用显式连接语法，而不是逗号=连接。

假设MySQL ≥ 8.0（但考虑到你对Charlieface的答案的挣扎，这是值得怀疑的），并且p.production和s.script都是相当大的文本字段，你可能会从SelVazi和Charlieface展示的方法中受益：

SELECT
  p.id AS production_id,
  p.production,
  s.id AS script_id,
  s.script
FROM (
    SELECT
      ps.*,
      COUNT(*) OVER (PARTITION BY ps.production_id) AS countCanonical
    FROM scripts s
    JOIN productions_scripts ps
      ON s.id = ps.script_id
    WHERE s.canonical = 1
) m
JOIN scripts s
  ON m.script_id = s.id
JOIN productions p
  ON m.production_id = p.id
WHERE m.countCanonical > 1
ORDER BY p.id;

字符串