先决条件:在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)
解决方法
视图实际上是一个保存的sql SELECT语句.至少,这是MS Access中保存的视图.你使用相同的内部变量A和B.恕我直言,他们正在混合.最后一行真的看起来像
SELECT A.ID FROM ATBL AS A WHERE EXISTS (SELECT 1 FROM (SELECT A.ID FROM ATBL AS A WHERE A.ID = 1 OR A.ID = 2) AS B WHERE B.ID=A.ID)
尝试更改一些内部名称,例如:
SELECT AA.ID FROM ATBL AS AA WHERE AA.ID = 1 OR AA.ID = 2
所以,最后一行看起来像
SELECT A.ID FROM ATBL AS A WHERE EXISTS (SELECT 1 FROM (SELECT AA.ID FROM ATBL AS AA WHERE AA.ID = 1 OR AA.ID = 2) AS B WHERE B.ID=A.ID)
所以,正如我们在这里看到的,MS Access甚至不知道如何隔离别名!