我有2个表(间隔和深度)与名称字段相关。我希望查询能够返回每个名称间隔表中不在间隔表中的所有深度(或不等于顶部和底部之间)。当间隔表中有多个名称记录时,我的查询失败(示例:名称字段中有2个"一个"记录)。
间隔
Name Top Bottom
one 2 3
one 5 7
two 2 3
three 3 4
深度
Name Depth
one 1
one 2
one 3
one 4
one 5
one 6
one 7
one 8
two 1
two 2
two 3
two 4
two 5
three 1
three 2
three 3
three 4
three 5
我的查询:
SELECT Intervals.Name, Depths.Depth
FROM Depths INNER JOIN Intervals ON Depths.Name = Intervals.Name
WHERE (((Depths.Depth) < [Intervals]![Top]
Or (Depths.Depth) > [Intervals]![Bottom]))
ORDER BY Intervals.Name, Depths.Depth;
我知道这失败了,因为将Where子句单独应用于每个名称记录的时间间隔。WHERE子句应适用于按名称相关的所有间隔记录,因此结果不包含间隔表中的任何自上而下的间隔。
我的查询输出:
Name Depth
one 1
one 1
one 2
one 3
one 4
one 4
one 5
one 6
one 7
one 8
one 8
three 1
three 2
three 5
two 1
two 4
two 5
所需的输出:所有深度不在间隔
Name Depth
one 1
one 4
one 8
two 1
two 4
two 5
three 1
three 2
three 5
您的问题的措辞建议not exists
,因此这可能对您有用:
select d.*
from depths as d
where not exists (select 1
from intervals as i
where i.name = d.name and d.depth between i.[top] and i.[bottom]
);