我有一个facilities
表。这些列是id
(主列(、sector
列(整数列(和located_in
列(可为空列,同一表上的引用id
列(。
大多数情况下,located_in
列为 NULL,但在某些情况下,facilities
可以位于其他facilities
的内部。
如果发生这种情况,我希望父设施不是结果的一部分(因此删除(。
一个设施只能位于另一个设施中,而另一个设施点不位于另一个设施点中,因此这不会深入 n 层。
数据集(在SQLFiddle上查看(:
+----+--------+------------+
| ID | sector | located_in |
+----+--------+------------+
| 1 | 2 | NULL |
| 2 | 2 | NULL |
| 3 | 4 | NULL |
| 4 | 1 | NULL |
| 5 | 5 | NULL |
| 6 | 2 | 2 |
+----+--------+------------+
查询(在 SQLFiddle 上查看(:
SELECT * FROM facilities WHERE sector = 2;
预期结果(在 SQLFiddle 上查看(:
+----+--------+------------+
| ID | sector | located_in |
+----+--------+------------+
| 1 | 2 | NULL |
| 6 | 2 | 2 |
+----+--------+------------+
实际结果(在 SQLFiddle 上查看(:
+----+--------+------------+
| ID | sector | located_in |
+----+--------+------------+
| 1 | 2 | NULL |
| 2 | 2 | NULL |
| 6 | 2 | 2 |
+----+--------+------------+
LEFT JOIN
:
SELECT DISTINCT f.*
FROM facilities f
LEFT JOIN facilities f2
ON f.id = f2.located_in
WHERE f.sector = 2
AND f2.ID IS NULL;
数据库<>小提琴演示
NOT EXISTS
和相关子查询来检查某个位置的任何子查询。
SELECT *
FROM facilities f1
WHERE f1.sector = 2
AND NOT EXISTS (SELECT *
FROM facilities f2
WHERE f2.sector = f1.sector
AND f2.located_in = f1.id);
如果跨部门关系也应该计算在内,也许您想删除平等部门的条件。
半差异。我首选的 SQL 公式:
select *
from facilities
where sector = 2
and id not in (select located_in from facilities where located_in is not null);