mysql - 查找仅属于特定办公室的相关邮政编码
问题描述
晕,我有两张桌子 office_postcodes 和 office
offices_postcodes = id,postcode,office_id
offices = id,offices,department
案例是:一个办公室有多个邮政编码,一个邮政编码属于多个办公室,示例:
postcode 0100036 has relation with office A
postcode 0100036 has relation with office B
postcode 0100035 only has relation with office A
postcode 0100037 has relation with office A
postcode 0100037 has relation with office B
postcode 0100039 only has relation with office A
我想找到属于办公室 A 但不属于办公室 B 的所有邮政编码,在这种情况下是 0100035 和 0100039。
我们可以这样做吗?这是我到目前为止所做的,
SELECT Count(`offices_postcodes`.postcode),
`offices_postcodes`.postcode
FROM `offices_postcodes`,
`offices`
WHERE `offices_postcodes`.office_id = `offices`.id
AND `offices`.department_id = 1
AND offices_postcodes.deleted_at IS NULL
GROUP BY `offices_postcodes`.postcode
HAVING Count(`offices_postcodes`.postcode) = 1
数据邮编:
id postcode office_id
1 0100036 271
2 0100036 275
3 0100035 271
4 0100037 271
5 0100037 275
6 0100039 271
数据办公室
id offices department_id
271 A 1
275 B 1
预期结果
postcode
0100035
0100039
解决方案
我认为您可以尝试使用JOIN
和连接条件op.office_id = o.id
架构(MySQL v5.7)
CREATE TABLE offices_postcodes(
id INT,
postcode VARCHAR(50),
office_id INT
);
INSERT INTO offices_postcodes VALUES (1,'0100036',271);
INSERT INTO offices_postcodes VALUES (2,'0100036',275);
INSERT INTO offices_postcodes VALUES (3,'0100035',271);
INSERT INTO offices_postcodes VALUES (4,'0100037',271);
INSERT INTO offices_postcodes VALUES (5,'0100037',275);
INSERT INTO offices_postcodes VALUES (6,'0100039',271);
CREATE TABLE offices(
id INT,
postcode VARCHAR(50),
department_id INT
);
INSERT INTO offices VALUES (271,'A',1);
INSERT INTO offices VALUES (275,'B',1);
查询 #1
SELECT op.postcode
FROM `offices_postcodes` op JOIN `offices` o
ON op.office_id = o.id
GROUP BY op.postcode
having Count(op.postcode) = 1;
| postcode |
| -------- |
| 0100035 |
| 0100039 |
推荐阅读
- javascript - 当在频道中发送消息时,Discord 机器人会给人一个角色
- flutter - 如何在 Flutter 中构建多个 UI 与一个项目略有不同的应用程序
- c++ - 返回类型为 ostream& 的函数的返回类型
- android - 列表视图未显示在 android 改造上,但编译器分配了 4MB 来编译 void android.widget.TextView。
- sinon - 使用参数验证对自身进行 Fake 的 CallOrder
- jquery - toggleClass 删除所有其他现有类
- excel - 基于值的重复行
- python - 如何将嵌套的 json 转换为数据框?
- vue.js - 我应该在 vue.js 中转储 data 属性中的所有变量吗
- python - 在字符串中查找单词