SQL一对多关系-如何根据多个属性来选择行? [英] SQL one-to-many relationship - How to SELECT rows depending on multiple to-many properties?

问题描述

MySQL数据库包含两个具有一对多关系的表:一个用户可以有许多设置:

A MySQL database contains two tables with a one-to-many relationship: One user can have many settings:

Users:
id    username    password
--------------------------
 1    Bob         123
 2    Alice       abc
 ...


Settings:
id   user_id   key     value
-----------------------------
 1   1         color   blue   // Bobs settings...
 2   1         theme   xy
 3   1         size    5
 4   2         size    5      // Alices settings...

问题:如何查找所有具有 color == blue AND size == 5 的用户?

Problem: How to find all users with color == blue AND size == 5?

使用 LEFT JOIN ，找到具有一个属性的用户没问题:

Using a LEFT JOIN it is no problem to find users with one property:

SELECT users.id FROM users LEFT JOIN settings ON users.id = settings.user_id WHERE settings.key = 'color' AND settings.value = 'blue'

但是，一次搜索两个设置时，这不起作用吗?

However, this does not work when searching for two settings at a time?

是否可以用一个语句解决这个问题?查询这些数据的最有效方法是什么?

Is it possible to solve this with a single statement? What is the most efficient way to query this data?

推荐答案

一种方法使用聚合和具有:

select s.user_id
from settings s
where (key, value) in (  ('color', 'blue'), ('size', '5') )
group by s.user_id
having count(*) = 2;

这假定没有重复的设置(如果这样，则需要使用 count(distinct)).

This assumes that there are no duplicate settings (if so, you would need to use count(distinct)).

这篇关于SQL一对多关系-如何根据多个属性来选择行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！