SQL: 이중 다대 다 관계에서 교차점 찾기

SQL: 이중 다대 다 관계에서 교차점 찾기

스키마 및 데이터의 간략화된 버전을 다음에 나타냅니다.

사용자:

id | name
 1 | Peter
 2 | Max
 3 | Susan

레스토랑:

id | name
 1 | Mario
 2 | Ali
 3 | Alfonzo
 4 | BurgerQueen

식기:

id | name
 1 | Burger
 2 | Pizza
 3 | Salad

users_module:

user_id | dish_id
      1 | 1
      2 | 1
      2 | 2
      3 | 2
      3 | 3

레스토랑_점포:

restaurant_id | dish_id
            1 | 2
            1 | 3
            2 | 1
            2 | 3
            3 | 1
            3 | 2
            3 | 3
            4 | 1

사용자, 레스토랑, 요리 등 세 가지 주체가 있습니다.그리고 둘 다 대 다의 관계.

• users-dishs 관계는 사용자가 무엇을 먹을 수 있는지를 정의합니다.
• 레스토랑-디시 관계는 레스토랑이 무엇을 제공할 수 있는지를 정의합니다.

입력으로 사용자 ID 목록이 있습니다.제가 지금 필요한 것은 목록에 있는 모든 사용자가 좋아하는 음식을 먹을 수 있는 모든 식당을 찾는 것입니다.

다음 쿼리를 고려합니다.

select u.name as user, group_concat(distinct r.name) as dishes
from users u
join users_dishes ud on ud.user_id = u.id
join restaurants_dishes rd on rd.dish_id = ud.dish_id
join restaurants r on r.id = rd.restaurant_id
group by u.id

이것은 각 사용자가 방문할 수 있는 모든 레스토랑을 보여줍니다.

user  | restaurants
Peter | Alfonzo,Ali,BurgerQueen
Max   | Alfonzo,Ali,BurgerQueen,Mario
Susan | Alfonzo,Ali,Mario

그래서 제가 필요한 건 세트의 교차점이에요.세 사용자 모두 Alfonzo와 Ali로 이동할 수 있습니다.하지만 피터는 마리아오에게 갈 수 없다.그리고 수잔은 버거퀸에 갈 수 없다.

(사용자 ID 1, 2, 3의 경우) 결과는 다음과 같습니다.

id | name
 2 | Ali
 3 | Alfonzo

ID 1, 2의 경우,

id | restaurant
 2 | Ali
 3 | Alfonzo
 4 | BurgerQueen

ID 2, 3의 경우,

id | restaurant
 1 | Mario
 2 | Ali
 3 | Alfonzo

다음 SQL 스크립트를 사용하여 스키마 및 샘플 데이터를 생성할 수 있습니다.

CREATE TABLE users (id INT AUTO_INCREMENT,name varchar(100),PRIMARY KEY (id));
INSERT INTO users(name) VALUES ('Peter'),('Max'),('Susan');
CREATE TABLE restaurants (id INT AUTO_INCREMENT,name varchar(100),PRIMARY KEY (id));
INSERT INTO restaurants(name) VALUES ('Mario'),('Ali'),('Alfonzo'),('BurgerQueen');
CREATE TABLE dishes (id INT AUTO_INCREMENT,name varchar(100),PRIMARY KEY (id));
INSERT INTO dishes(name) VALUES ('Burger'),('Pizza'),('Salad');
CREATE TABLE users_dishes (user_id INT,dish_id INT,PRIMARY KEY (user_id, dish_id),INDEX (dish_id, user_id));
INSERT INTO users_dishes(user_id, dish_id) VALUES (1,1),(2,1),(2,2),(3,2),(3,3);
CREATE TABLE restaurants_dishes (restaurant_id INT,dish_id INT,PRIMARY KEY (restaurant_id, dish_id),INDEX (dish_id, restaurant_id));
INSERT INTO restaurants_dishes(restaurant_id, dish_id) VALUES (1,2),(1,3),(2,1),(2,3),(3,1),(3,2),(3,3),(4,1);

db-fiddle.com에 SQL-fiddle도 준비했습니다.

MySQL 5.7 및 MariaDB 10.1과 호환되는 솔루션이 필요하다는 점도 언급해야 합니다.

전형적인 관계 분할.가장 쉬운 방법 중 하나는 다음과 같습니다.

select *
from restaurants r
where not exists (
  select *
  from users u
  where not exists (
    select *
    from users_dishes ud
    join restaurants_dishes rd on ud.dish_id = rd.dish_id
    where ud.user_id = u.id
    and rd.restaurant_id = r.id
  )
  and u.id in (1, 2, 3)
)

데모를 해 주세요.즉, 특정 레스토랑에 요리가 없는 사용자가 있다면 해당 레스토랑은 모든 사용자를 수용할 수 없습니다.그래서 우리는 손님이 없는 레스토랑과 그 레스토랑에 요리가 없는 레스토랑을 얻고 싶습니다.

저는 당신의 질의를 레스토랑 이름별로 그룹화하여 각각 식사할 수 있는 사용자를 카운트하도록 수정하고 다음과 같은 조건을 추가했습니다.

select r.id, r.name as restaurant
from users u
join users_dishes ud on ud.user_id = u.id
join restaurants_dishes rd on rd.dish_id = ud.dish_id
join restaurants r on r.id = rd.restaurant_id
group by r.id, r.name
having count(distinct u.id) = (select count(*) from users);

결과:

| id  | restaurant |
| --- | ---------- |
| 2   | Ali        |
| 3   | Alfonzo    |

데모 보기
다음과 같은 조건을 추가하여 사용자 목록을 확인할 수 있습니다.

select r.id, r.name as restaurant
from users u
join users_dishes ud on ud.user_id = u.id
join restaurants_dishes rd on rd.dish_id = ud.dish_id
join restaurants r on r.id = rd.restaurant_id
where u.id in (1, 2, 3)
group by r.id, r.name
having count(distinct u.id) = 3;

문제를 바꿔보자: 각 사용자에게 최소한 한 가지 요리를 제공하는 식당을 찾아보자.다음과 같이 나타낼 수 있습니다.

SELECT *
FROM restaurants
WHERE id IN (
    SELECT restaurants_dishes.restaurant_id
    FROM restaurants_dishes
    JOIN users_dishes ON restaurants_dishes.dish_id = users_dishes.dish_id
    WHERE users_dishes.user_id IN (1, 2, 3)         -- <--------------+
    GROUP BY restaurants_dishes.restaurant_id       --                |
    HAVING COUNT(DISTINCT users_dishes.user_id) = 3 -- this matches --+
)

언급URL : https://stackoverflow.com/questions/55148813/sql-find-intersection-in-a-double-many-to-many-relatiion