MySQL查询加入同一张表

Question

我有两个表:

用户:

ID  |   title
0   |   Adam
1   |   Ben
2   |   Sara
3   |   Camille

用户元:

ID  |   user_ID |   meta_key    |   meta_value
0   |       0   |   'weight'    |   '80'
1   |       0   |   'sex'       |   'male'
2   |       1   |   'weight'    |   '93'
3   |       1   |   'sex'       |   'male'
4   |       2   |   'weight'    |   '57'
5   |       2   |   'sex'       |   'female'
6   |       3   |   'weight'    |   '71'
7   |       4   |   'sex'       |   'female'

现在我想获取所有体重超过 90 岁的男性用户，我的查询:

SELECT u.* FROM users AS u
RIGHT JOIN users_meta AS weight on(u.ID=weight.user_ID)
RIGHT JOIN users_meta AS sex on(u.ID=sex.user_ID)
WHERE
    weight.meta_key='weight' AND
    weight.meta_key>90 AND
    sex.meta_key='sex' AND
    sex.meta_value ='male'

上述查询对我来说效果很好，但我很好奇是否有更好的方法。如果每个用户都有大量元数据，那么您最终会在同一个表上进行多个连接以检查不同的值。

Answer 1

您可以根据 user_id 轻松聚合 users_meta 表，并通过应用适当的 HAVING 子句仅保留满足您条件的那些用户 ID。然后为这组所需的用户 ID 选择用户。

select * 
from users
where user_id in
(
  select user_id
  from users_meta
  where meta_key in ('weight', 'sex') -- not necessary, but probably faster
  group by user_id
  having sum(meta_key = 'weight' and meta_value > 90) > 0
     and sum(meta_key = 'sex' and meta_value = 'male') > 0
);