mysql - 嵌套选择查询更高效

Question

我有user&user_code表有一对多的关系。请找到下表结构。

用户表

 id email   username    date
679 [e-mail]   sathis  4/20/2019 9:04
679 [e-mail]   sathis  4/20/2019 9:04
679 [e-mail]   sathis  4/20/2019 9:04
679 [e-mail]   sathis  4/20/2019 9:04
679 [e-mail]   sathis  4/20/2019 9:04
679 [e-mail]   sathis  4/20/2019 9:04
679 [e-mail]   sathis  4/20/2019 9:04
679 [e-mail]   sathis  4/20/2019 9:04
680 [e-mail]   ram 4/20/2019 9:04
680 [e-mail]   ram 4/20/2019 9:04
680 [e-mail]   ram 4/20/2019 9:04
681 [e-mail]   Steve   4/20/2019 9:04
681 [e-mail]   Steve   4/20/2019 9:04
681 [e-mail]   Steve   4/20/2019 9:04
681 [e-mail]   Steve   4/20/2019 9:04
681 [e-mail]   Steve   4/20/2019 9:04

User_Code 表

user_id code
679 J039
679 J080
679 J320
679 J54L
679 K31P
679 L05C
679 T030
679 V150
680 J039
680 J080
680 J320
681 ABC12
681 CD123
681 opo123
681 qw123
681 ieu12

如果我给出username（来自 user 表），那么它应该检查code（来自 user_code 表）并且相同的代码应该检查它是否呈现给其他一些user_id（来自 user_code 表）并且应该返回那个user_ids。

例如。

  id    username
679 sathis
680 ram

从上表中，如果我得到sathis输入。我应该得到ram输出结果。因为Sathis's代码也呈现为Ram也。

请在下面找到我的 sql 嵌套查询

select id,username from users
where 
id in (
select user_id from user_code
where
code in (
select code from user_code
where user_id = ( select id from user where username = 'sathis')))

我怎样才能更有效地简化这个查询，

标签： mysql

Answer 1

为了提高性能，您可以避免 IN 子句并在子查询上使用内部联接

select id, username 
from users 
inner join (
  select distinct user_id 
  from User_Code 
  inner join  (
    select code 
    from User_Code 
    inner join User ON User_Code.user_id = user.id
    where  username = 'sathis'
  ) t on t.code = User_Code.code
) t2 on t2.user_id = users.id