时间戳

首页 > 解决方案 > 从 3 个表中选择用户尚未绑定的位置

mysql - 从 3 个表中选择用户尚未绑定的位置

问题描述

我有 3 张桌子

用户信息

ID 姓名
1 鲍勃
2 简
3 汤姆

地点

ID 姓名
1 测试1
2 测试2
3 测试3
4 测试4

用户位置

用户身份 位置ID
1 1
1 2
2 3

基本上我想要实现的是提取用户尚未绑定的位置名称。

在上面的列表中，Bob 有 2 个位置，分别为“test 1”和“test 2”，但他还没有“test 3”或“test 4”。我只希望数据返回测试 3 和 4，因为 Bob 没有这些数据。

对于 Jane She 只有测试 3 有界，但其余 3 都没有

最初我尝试过这个并且它有点工作。然而，每次另一个用户获得一个无限位置时，它都会从列表中删除。我不确定如何在所有这些中添加用户 ID，因此它仅特定于该用户。

SELECT `name` FROM `locations` 
WHERE `id` NOT IN (SELECT `locationID` FROM  `user_locations`)

标签： mysql

解决方案

创建用户和位置表的笛卡尔积（交叉连接），然后使用外连接允许我们在 user_locations 中查找尚未匹配的行：

select
      user_info.ID AS UserID
    , locations.ID AS locationID
from user_info
cross join locations
left outer join user_locations on user_info.id = user_locations.userid
                              and locations.id = user_locations.locationid
where user_locations.userid IS NULL
and user_info.name = 'bob'

SQL小提琴

MySQL 5.6 架构设置：

CREATE TABLE user_info(
   id   INTEGER  NOT NULL PRIMARY KEY 
  ,name VARCHAR(4) NOT NULL
);
INSERT INTO user_info(id,name) VALUES (1,'bob');
INSERT INTO user_info(id,name) VALUES (2,'jane');
INSERT INTO user_info(id,name) VALUES (3,'tom');

CREATE TABLE locations(
   id   INTEGER  NOT NULL PRIMARY KEY 
  ,name VARCHAR(5) NOT NULL
);
INSERT INTO locations(id,name) VALUES (1,'Test1');
INSERT INTO locations(id,name) VALUES (2,'Test2');
INSERT INTO locations(id,name) VALUES (3,'Test3');
INSERT INTO locations(id,name) VALUES (4,'Test4');

CREATE TABLE user_locations(
   userID     INTEGER  NOT NULL
  ,locationID INTEGER  NOT NULL
);
INSERT INTO user_locations(userID,locationID) VALUES (1,1);
INSERT INTO user_locations(userID,locationID) VALUES (1,2);
INSERT INTO user_locations(userID,locationID) VALUES (2,3);

查询 1：

select
      user_info.ID AS UserID
    , locations.ID AS locationID
from user_info
cross join locations
left outer join user_locations on user_info.id = user_locations.userid
                              and locations.id = user_locations.locationid
where user_locations.userid IS NULL
order by 1,2

结果：

| UserID | locationID |
|--------|------------|
|      1 |          3 |
|      1 |          4 |
|      2 |          1 |
|      2 |          2 |
|      2 |          4 |
|      3 |          1 |
|      3 |          2 |
|      3 |          3 |
|      3 |          4 |

推荐阅读