说明？？？ [英] Explanation???
问题描述
我找到了一些我无法解释的东西.....
我有两张桌子：
CLIENTS_COMPANY_A：
CodA Int。
NameA Text（40）
NIFA TEXT（15）
CLIENTS_COMPANY_B：
CodB Int。
NameB文字（40）
NIFB文字（15）
A想知道公司A的哪些客户不在B公司，下一个
SQL :(使用字段NIF）
当然A上有客户在B上找不到NIF'
select * from clients_company_A
其中NIFA不在（选择来自clients_company_B的NIFB
它返回0行!!!!!!
NIFA和NIFB都有NULL值，所以我重试：
select * from clients_company_A
其中NIFA<> "
和NIFA不在（从clients_company_B中选择NIFB，其中NIFB<>""）
这个回馈所有行我在哪里寻找，但我不明白
为什么第一个SQL不起作用。
我也是这样做的Mysql和第一个SQL工作正常。
这有什么解释吗？访问是否正常工作？
先谢谢，Jon
I''ve found something in access I can''t explain.....
I have two tables:
CLIENTS_COMPANY_A:
CodA Int.
NameA Text(40)
NIFA TEXT(15)
CLIENTS_COMPANY_B:
CodB Int.
NameB Text(40)
NIFB TEXT(15)
A want to know which clients of company A aren''t on company B, with the next
SQL: (using the field NIF)
Of course there is clients on A which NIF''s aren''t found on B
select * from clients_company_A
where NIFA not in (select NIFB from clients_company_B)
It gives back 0 rows !!!!!!
There are NULL values on NIFA and in NIFB, so I retried:
select * from clients_company_A
where NIFA <> ""
and NIFA not in (select NIFB from clients_company_B where NIFB<>"")
This one gives back all the rows I where looking for, but I don''t understand
why the first SQL didn''t work.
I''ve trid the same on Mysql and the first SQL works ok.
Is there any explanation for this? Is access working properly?
Thanks in advance, Jon
推荐答案
" Jon Berraondo" <乔*********** @ elektra-sa.es>在消息中写道
news：dn ********** @ domitilla.aioe.org ...
"Jon Berraondo" <jo***********@elektra-sa.es> wrote in message
news:dn**********@domitilla.aioe.org...
我在访问中发现了一些东西无法解释.....
我有两张桌子：
CLIENTS_COMPANY_A：
CodA Int。
NameA Text（40）
NIFA TEXT（15）
CLIENTS_COMPANY_B：
CodB Int。
NameB文字（40）
NIFB TEXT（15）
想知道A公司的哪些客户不在B公司，用
下一页
SQL :(使用现场NIF）
当然有客户在A上找不到哪个NIF'
从clients_company_A中选择*
其中NIFA不在（从clients_company_B中选择NIFB）
它给出了返回0行!!!!!!
NIFA和NIFB都有NULL值，所以我重试：
select * from clients_company_A
其中NIFA< ;>
和NIFA不在（从Customers_company_B中选择NIFB，其中NIFB<>""）
这个给出了我寻找的所有行，但我不知道't
理解为什么第一个SQL不起作用。
我在Mysql上做了同样的事情，第一个SQL工作正常。
对此有什么解释吗？访问是否正常工作？
I''ve found something in access I can''t explain.....
I have two tables:
CLIENTS_COMPANY_A:
CodA Int.
NameA Text(40)
NIFA TEXT(15)
CLIENTS_COMPANY_B:
CodB Int.
NameB Text(40)
NIFB TEXT(15)
A want to know which clients of company A aren''t on company B, with the
next
SQL: (using the field NIF)
Of course there is clients on A which NIF''s aren''t found on B
select * from clients_company_A
where NIFA not in (select NIFB from clients_company_B)
It gives back 0 rows !!!!!!
There are NULL values on NIFA and in NIFB, so I retried:
select * from clients_company_A
where NIFA <> ""
and NIFA not in (select NIFB from clients_company_B where NIFB<>"")
This one gives back all the rows I where looking for, but I don''t
understand
why the first SQL didn''t work.
I''ve trid the same on Mysql and the first SQL works ok.
Is there any explanation for this? Is access working properly?
您是否将文本字段设置为允许零长度字符串？
Keith。
www.keithwilby.com
嗯，你所有的第二个声明都是排除非空的NIFA
字符串并且不应该影响Null值（无论如何都会被排除为
他们不等于。）
个人称为不在...没有优化Access我会和你一起去
SELECT
*
FROM
clients_company_A
LEFT OUTER JOIN
clients_company_B
ON
NIFA = NIFB
WHERE
NIFB不是空的
-
Terry Kreft
" Jon Berraondo" <乔*********** @ elektra-sa.es>在消息中写道
news：dn ********** @ domitilla.aioe.org ...
Hmm, all your second statement does is exclude NIFAs which are not empty
strings and shouldn''t affect Null values (which would be excluded anyway as
they wouldn''t equate).
Personally as "not in" is not optimised in Access I would have gone with
SELECT
*
FROM
clients_company_A
LEFT OUTER JOIN
clients_company_B
ON
NIFA = NIFB
WHERE
NIFB Is Not Null
--
Terry Kreft
"Jon Berraondo" <jo***********@elektra-sa.es> wrote in message
news:dn**********@domitilla.aioe.org...
我在访问中发现了一些东西无法解释.....
我有两张桌子：
CLIENTS_COMPANY_A：
CodA Int。
NameA Text（40）
NIFA TEXT（15）
CLIENTS_COMPANY_B：
CodB Int。
NameB文字（40）
NIFB TEXT（15）
想知道A公司的哪些客户不在B公司，用
下一页
SQL :(使用现场NIF）
当然有客户在A上找不到哪个NIF'
从clients_company_A中选择*
其中NIFA不在（从clients_company_B中选择NIFB）
它给出了返回0行!!!!!!
NIFA和NIFB都有NULL值，所以我重试：
select * from clients_company_A
其中NIFA< ;>
和NIFA不在（从Customers_company_B中选择NIFB，其中NIFB<>""）
这个给出了我寻找的所有行，但我不知道't
理解为什么第一个SQL不起作用。
我在Mysql上做了同样的事情，第一个SQL工作正常。
对此有什么解释吗？访问是否正常工作？
先谢谢，Jon
I''ve found something in access I can''t explain.....
I have two tables:
CLIENTS_COMPANY_A:
CodA Int.
NameA Text(40)
NIFA TEXT(15)
CLIENTS_COMPANY_B:
CodB Int.
NameB Text(40)
NIFB TEXT(15)
A want to know which clients of company A aren''t on company B, with the
next
SQL: (using the field NIF)
Of course there is clients on A which NIF''s aren''t found on B
select * from clients_company_A
where NIFA not in (select NIFB from clients_company_B)
It gives back 0 rows !!!!!!
There are NULL values on NIFA and in NIFB, so I retried:
select * from clients_company_A
where NIFA <> ""
and NIFA not in (select NIFB from clients_company_B where NIFB<>"")
This one gives back all the rows I where looking for, but I don''t
understand
why the first SQL didn''t work.
I''ve trid the same on Mysql and the first SQL works ok.
Is there any explanation for this? Is access working properly?
Thanks in advance, Jon
" Terry Kreft" < TE ********* @ mps.co.uk>写在
新闻：Xj ******************** @ karoo.co.uk：
"Terry Kreft" <te*********@mps.co.uk> wrote in
news:Xj********************@karoo.co.uk:
个人为不在在Access中我没有优化
Personally as "not in" is not optimised in Access I would have
gone with
谁说它没有优化？不，SHOWPLAN没有显示任何
的优化，但那是因为SHOWPLAN从未实现处理任何形式的子查询。这并不意味着查询
优化器没有优化子查询，只是你不能找到
如何使用SHOWPLAN。
-
David W. Fenton http： //www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Who says it''s not optimized? No, SHOWPLAN does not show any
optimization for it, but that''s because SHOWPLAN never implemented
handling of any form of subqueries. That doesn''t mean the query
optimizer is not optimizing subqueries, only that you can''t find out
how using SHOWPLAN.
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
这篇关于说明？？？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
