试试这个：

SELECT b.movie_title, x.movie_id, MAX( x.free_cnt ) AS free, MAX( x.plus_cnt ) AS plus, 
           ( MAX( x.free_cnt ) + ( MAX( x.plus_cnt ) * 3 ) ) AS score, ( MAX( x.free_cnt ) + MAX( x.plus_cnt ) ) AS total
    FROM ( SELECT a.movie_id,
                  SUM( IF( c.subsc_status = '0', 1, 0 ) ) AS free_cnt, 
                  SUM( IF( c.subsc_status = '1', 1, 0 ) ) AS plus_cnt
           FROM subtitles_request a1
           JOIN users c on c.email=a1.email
           WHERE c.subsc_status in ('0','1')
           GROUP BY a.movie_id
         ) AS x
    LEFT JOIN movies b on x.movie_id = b.id
    GROUP BY movie_title, movie_id
    ORDER BY total DESC
    LIMIT 10

也许我简化得太多了。此外，我不习惯只对一些非聚合字段进行分组，因此我将movie\u id添加到分组依据中，从而稍微更改了您的查询（如果两部电影的名称相同，但id不同，那么在您的原始查询中只会返回一个id，但我猜（作为mysql新手，我真的不知道）计数将为他们两个一起）。
hth，设置

好吧，我已经检查了你的子查询：

SELECT b.id as movie_id, b.movie_title as movie_title, COUNT(*) AS free_cnt, 0 as plus_cnt
FROM subtitles_request a1
LEFT JOIN movies b on a1.movie_id=b.id
JOIN users c on c.email=a1.email
WHERE c.subsc_status='0'
GROUP BY b.movie_title
UNION ALL

SELECT d.id as movie_id, d.movie_title as movie_title,  0 as free_cnt, COUNT(*) AS plus_cnt
FROM subtitles_request a2
LEFT JOIN movies d on a2.movie_id=d.id
JOIN users e on e.email=a2.email
WHERE e.subsc_status='1'
GROUP BY d.movie_title

“union all”旁边的语句可以替换为一个条件为的语句 c.subsc_status IN('0','1') . 你可以尝试使用case-when语句 0 as free_cnt, COUNT(*) AS plus_cnt ，就像 IFNULL((CASE WHEN e.subsc_status='1' THEN COUNT(*)),0) as free_cnt . 这不是一个复杂的sql语句，我不认为查询它会花费太多时间。数据太多了吗？事实上，我也是一个新手，但我只是有一些经验。如果不行，请原谅我。