将嵌套选择转换为连接 [英] Converting nested select into join

问题描述

我的查询是

select COUNT(*) from result 
where test_id in (select test_id 
                    from test_schedule 
                    where scheduler_id in (select user_id 
                                           from users 
                                           where user_type=1))

推荐答案

SELECT COUNT(*)
FROM result r
JOIN (SELECT DISTINCT test_id
      FROM test_schedule s
      JOIN users u ON s.scheduler_id = u.user_id
      WHERE u.user_type = 1) s
USING (test_id)

DISTINCT 是必要的，以防止行与其他表中匹配的所有行相乘.

The DISTINCT is necessary to keep rows from being multiplied by all the rows in the other tables that match.

这篇关于将嵌套选择转换为连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！