帮助请求“NOT EXISTS”的有效替代方案或“不在” [英] Help Request for an efficient alternative to "NOT EXISTS" or "NOT IN"

问题描述

在MS Access中，我正在寻找一种有效的方法来返回记录，其中与另一个表相比，一个表中的字段值不存在。我可以使用NOT EXISTS或NOT IN来执行此操作，但它们始终可以运行。


我一直在寻找左脑和右脑，但是在这个过程中已经摧毁了太多的脑细胞，现在我完全糊涂了！


我有两张桌子（描述鸟群的情况！）。


Bird Sightings主表（表1）中的相关字段是SPECIES，SQUARE和TETRAD，描述了非常多的鸟类物种和2k地理广场（Square和Tetrad）。见表。


表2包含字段SQUARE，TETRAD和FLAG，列出县内及周围所有可能的SQUARES和TETRAD，其中flag字段中的Y表示四分之一完全位于县界。


我希望能够选择SPECIES，SQUARE和TETRAD来识别那些物种没有目击的SQUARE和TETRAD（对于FLAGYTetrads只有）。


请问有人建议使用效率最高的SQL吗？


非常感谢您的预期

Phil Davis

Hi, in MS Access, I am looking for an efficient way of returning records where field values in one table are NOT present when compared to another table. I can do this using NOT EXISTS or NOT IN but they take for ever to run.

I''ve been looking a LEFT and RIGHT JOINs but have destroyed too many brain cells in the process and am now totally confused !

I have two tables (that describe Bird Populations as it happens!).

The relevant fields in the main Bird Sightings table (Table 1) are SPECIES, SQUARE and TETRAD which describe very many bird species sightings and the 2k geograpical squares (Square and Tetrad) they were seen in.

Table two contains fields SQUARE, TETRAD and a FLAG that lists all possible SQUARES and TETRADs in and around the County where a Y in the flag field denotes the tetrad being wholly within the county boundary.

I''d like to be able to select SPECIES, SQUARE and TETRAD identifying those SQUAREs and TETRADs where the Species has no sightings (for FLAG "Y" Tetrads only).

Could anybody suggest the most efficient SQL to do this please?

Many thanks in anticipation
Phil Davis

推荐答案

两个表中都缺少主键。

请确认两个表之间的关系是在[square]字段上nd [Tetrad]？没有真正的关系...我们只能猜测。


Table_one在[square]和[Tetrad]字段中存储什么？


Table_two在[square]和[Tetrad]字段中存储什么？


-z

You''re lacking a primary key in both tables.
Please confirm the relationship between the two tables would be on the fields [square] and [Tetrad]? There is no real relationship given... we can only guess.

What does Table_one store in the fields [square] and [Tetrad]?

What does Table_two store in the [square] and [Tetrad] fields?

-z

如果你的意思是想找到所有没有任何目击的地方，那么只要另一边为空，你需要一个简单的左/右外连接。


但如果你的意思是列出的地点列表没有物种的目击，那么你需要一个第三物种表，交叉连接到这些位置，然后左边连接到目击表，其中目击表为空。

If you mean you want to find all locations where there have been no sightings whatsoever, then a simple left/right outer join is all you need where the other side is null.

But if you mean a listing of locations where there have been no sightings by species, then you need a third species table, cross joined to the locations and then left joined to the sightings table where the sightings table is null.

你好zmbd，非常感谢你的回复。我猜你已经推断出没有主键 - 你的权利！ - 按处理NOT EXISTS或NOT IN条款所花费的时间？


表1 Square和Tetrad字段包含10k方形id的军械测量（例如SP00，SU05，ST69，SO50）和Tetrad id（单个字符AN或PZ）标识英国境内2k平方的地面。


表2包含所有可能组合的相同内容（对于Gloucesestershire）是完全包含在格子中的683个2k方格 - 用Y标记。旗帜 - 以及成千上万的所有目击事件，包括成千上万的普通物种（例如Blackbird）。


我没有为table1分配主键，因为几乎没有表1中任何一个领域的独特性，但我想我可以在表2中组成一个四分场（例如SP00A），所有这些都是唯一的。我可能在这里放弃了我缺乏数据库设计知识的事情！？

Hi zmbd, many thanks for your reply. I guess you''ve deduced there is no primary key - your right! - by the time taken to process a NOT EXISTS or NOT IN clause?

Table 1 Square and Tetrad fields contain the Ordnance Survey 10k square id (eg SP00, SU05, ST69, SO50) and Tetrad id (a single character A-N or P-Z) that identifies a 2k square piece of ground within the UK.

Tables 2 contains the same content with all possible combinations (for Gloucesestershire) which is 683 2k squares wholly contained in Glos - marked with a "Y" flag - and hundreds of thousands of all sightings including thousands of sightings for certain commoner species (eg Blackbird).

I didn''t assign a primary key to table1 because there is virtually no uniqueness in any field of table 1 but I guess I could make up a tetradid field (eg SP00A) in table 2 all of which would be unique. I''m giving away my lack of database design knowledge here probably!?

这篇关于帮助请求“NOT EXISTS”的有效替代方案或“不在”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！