选择通用交互mysql

Question

我有三张桌子：

[user]
id | name  |  level
10 | John  |   1
11 | Josh  |   2
12 | Mary  |   3

[level_interaction]
level | interact_with
  1   |     1
  1   |     2

  2   |     1
  2   |     2
  2   |     3

  3   |     2
  3   |     3

room_id | user_id
1       |  10
1       |  11

2       |  11
2       |  12

3       | 11  

我想要的是选择哪个级别可以与房间互动。

• 如果我检查一下room 1，答案应该是1级和2级(因为John是1级，他只能与2级交互)，尽管作为级别2的可以与3级交互。
• 如果我查一下 3房间，因为只有乔什在那里，答案应该是12和3。
• 如果我选择房间2，答案应该是2级和3级。

基本上，我想要的是一种方式，选择一个共同的互动水平为成员的房间！

检查46rXZdEVEA?language=mysql上的表/数据集

Answer 1

使用group by li.interact_with子句中的一个条件连接表和HAVING：

select li.interact_with
from room r
inner join user u on u.id = r.user_id
inner join level_interaction li on li.level = u.level
where r.room_id = 1
group by li.interact_with
having count(*) = (select count(user_id) from room where room_id = 1);

见演示。

结果(用于room_id = 1)：

> | interact_with |
> | ------------: |
> |             1 |
> |             2 |

Answer 2

您没有提到您正在使用的MySQL版本，所以我假设它是现代版本。在MySQL 8.x中，您可以使用CTE。

例如：

with
x as (
  select u.name, i.interact_with as level
  from room r
  join user u on u.id = r.user_id
  join level_interation i on i.level = u.level
  where r.room_id = 1 -- this is the starting room
)
select level
from x 
group by level
having count(distinct name) = (
  select count(distinct name) from x
)