MS Access：WHERE-EXISTS 子句不适用于视图？
先决条件： 在 MS Access 2010 中创建以下表：
CREATE TABLE ATBL(ID INT);
INSERT INTO ATBL(ID) VALUES (1);
INSERT INTO ATBL(ID) VALUES (2);
INSERT INTO ATBL(ID) VALUES (3);
CREATE TABLE BTBL(ID INT);
INSERT INTO BTBL(ID) VALUES (1);
INSERT INTO BTBL(ID) VALUES (2);
还创建一个名为 BVIEW 的视图，该视图使用以下 SELECT 语句：
SELECT A.ID FROM ATBL AS A WHERE A.ID = 1 OR A.ID = 2
现在 BVIEW 应该具有与 BTBL 相同的内容。然而，以下两个查询将返回不同的结果：
SELECT A.ID FROM ATBL AS A WHERE EXISTS (SELECT 1 FROM BTBL AS B WHERE B.ID=A.ID)
SELECT A.ID FROM ATBL AS A WHERE EXISTS (SELECT 1 FROM BVIEW AS B WHERE B.ID=A.ID)
第一个查询返回两条记录（1 和 2），但第二个查询返回 ATBL 中的所有记录。这是怎么回事？我错过了什么吗？
Prerequisites: In MS Access 2010 create the following tables:
CREATE TABLE ATBL(ID INT);
INSERT INTO ATBL(ID) VALUES (1);
INSERT INTO ATBL(ID) VALUES (2);
INSERT INTO ATBL(ID) VALUES (3);
CREATE TABLE BTBL(ID INT);
INSERT INTO BTBL(ID) VALUES (1);
INSERT INTO BTBL(ID) VALUES (2);
Also create a view called BVIEW which uses the following SELECT statement:
SELECT A.ID FROM ATBL AS A WHERE A.ID = 1 OR A.ID = 2
Now BVIEW should have the same contents as BTBL. Nevertheless the following two queries will return different results:
SELECT A.ID FROM ATBL AS A WHERE EXISTS (SELECT 1 FROM BTBL AS B WHERE B.ID=A.ID)
SELECT A.ID FROM ATBL AS A WHERE EXISTS (SELECT 1 FROM BVIEW AS B WHERE B.ID=A.ID)
The first query returns two records (1 and 2), but the second query returns all records from ATBL. What's wrong here? Am I missing something?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
视图实际上是保存的 SQL SELECT 语句。至少，MS Access 中保存的视图是这样的。并且您使用相同的内部变量 A 和 B。恕我直言，它们正在混合。最后一行实际上看起来像
尝试更改一些内部名称，例如：
所以，最后一行将看起来像
所以，正如我们在这里看到的，MS Access 甚至不知道如何隔离别名！
A view is really a saved SQL SELECT statement. At least, this is what a saved view in MS Access is. And you use the same inner variables A and B. IMHO, they are getting mixed. The last line really looks as
Try to change some inner names, for example:
So, the last line will look as
So, as we see here, MS Access even doesn't know how to isolate aliases!