查询过于复杂。如何编码不正确的声明 [英] Query too complex. How to code not like statement properly
问题描述
。我正在尝试查看所有的streets.type值为null,但是
然后排除那些对于S.type而言为空的STREETNAME值但是
这些值是真的好的。查询如下:
SELECT S.ID,S.ID2,S.PREFIX,S.STREETNAME,S.TYPE
来自街道的S < br $>
WHERE
S.STREETNAME不喜欢* HWY110 * AND
S.STREETNAME不喜欢* HWY111 * AND
S.STREETNAME不喜欢* HWY112 * AND
S.STREETNAME不喜欢* HWY113 * AND
S.STREETNAME不喜欢* HWY114 * AND
S.STREETNAME不喜欢* HWY115 * AND
S.STREETNAME不喜欢* HWY116 * AND
S.STREETNAME不喜欢* HWY117 * AND
S.STREETNAME不喜欢* HWY118 * AND
S.STREETNAME不喜欢* HWY119 *和
等大约250多个值。
AND((S.TYPE)是空的));
现在,一旦我得到这些值中的大约50个左右,我得到查询太多
复杂,我该如何实现?
TIA
2005年5月21日星期六23:07:59 -0700,ynott< yn *** @ hotmail.com>写道:
将值放在表格中。然后写:
SELECT ... WHERE s.StreetName not in(select< FieldName> from
< NewTable>)
-Tom。
这里显然是新手。无论如何,我对如何解决这个问题感到有些困惑。我对我正在尝试创建的表进行查询。我正在尝试查看所有的streets.type值为null,但是
然后排除那些对于S.type而言为空的STREETNAME值,但是
确实没问题。查询如下:
SELECT S.ID,S.ID2,S.PREFIX,S.STREETNAME,S.TYPE
来自街道作为S
WHERE
S.STREETNAME不喜欢* HWY110 * AND
S.STREETNAME不喜欢* HWY111 * AND
S.STREETNAME不喜欢* HWY112 * AND
S.STREETNAME不喜欢* HWY113 * AND
S.STREETNAME不喜欢* HWY114 * AND
S.STREETNAME不喜欢* HWY115 * AND
S.STREETNAME不喜欢* HWY116 * AND
S.STREETNAME不喜欢* HWY117 * AND
S.STREETNAME不喜欢* HWY118 * AND
S.STREETNAME不喜欢* HWY119 *还有大约250多个值。
AND((S.TYPE)Null));
现在,一旦我到达大约50个左右这些值,我得到查询太复杂,我怎么能做到这一点?
TIA
完美。 Thx
2005年5月21日星期六23:24:29 -0700,Tom van Stiphout
< no ********* ****@cox.net>写道:
2005年5月21日星期六23:07:59 -0700,ynott< yn *** @ hotmail.com>写道:
将值放在表中。然后写:
SELECT ... WHERE s.StreetName not in(select< FieldName> from
< NewTable>)
-Tom。
这里显然是新手。无论如何,我对如何解决这个问题感到有些困惑。我对我正在尝试创建的表进行查询。我正在尝试查看所有的streets.type值为null,但是
然后排除那些对于S.type而言为空的STREETNAME值,但是
确实没问题。查询如下:
SELECT S.ID,S.ID2,S.PREFIX,S.STREETNAME,S.TYPE
来自街道作为S
WHERE
S.STREETNAME不喜欢* HWY110 * AND
S.STREETNAME不喜欢* HWY111 * AND
S.STREETNAME不喜欢* HWY112 * AND
S.STREETNAME不喜欢* HWY113 * AND
S.STREETNAME不喜欢* HWY114 * AND
S.STREETNAME不喜欢* HWY115 * AND
S.STREETNAME不喜欢* HWY116 * AND
S.STREETNAME不喜欢* HWY117 * AND
S.STREETNAME不喜欢* HWY118 * AND
S.STREETNAME不喜欢* HWY119 *还有大约250多个值。
AND((S.TYPE)Null));
现在,一旦我到达大约50个左右这些值,我得到查询太复杂,我怎么能做到这一点?
TIA
Tom
你试过这个吗?它慢吗?我找到了NOT IN非常,非常好b / b
慢。出于这个原因,我同意将值放入
表中,但我建议在SQL中使用JOIN和WHERE
CriteriaTable.StreetName为Null。 br />
但也许这个想法不再相关,SQL和JET或其他什么
会优化NOT IN条件,以便它的执行速度快?
Novice here obviously. Anyway, I''m a bit confused about how to
approach this. I have a query against a table that I''m trying to
create. I''m trying to view all streets.type values that are null, but
then exclude those STREETNAME values that are null for the S.type but
that are really okay. The query goes like:
SELECT S.ID, S.ID2, S.PREFIX, S.STREETNAME, S.TYPE
FROM streets as S
WHERE
S.STREETNAME Not Like "*HWY110*" AND
S.STREETNAME Not Like "*HWY111*" AND
S.STREETNAME Not Like "*HWY112*" AND
S.STREETNAME Not Like "*HWY113*" AND
S.STREETNAME Not Like "*HWY114*" AND
S.STREETNAME Not Like "*HWY115*" AND
S.STREETNAME Not Like "*HWY116*" AND
S.STREETNAME Not Like "*HWY117*" AND
S.STREETNAME Not Like "*HWY118*" AND
S.STREETNAME Not Like "*HWY119*" AND
etc for about 250 more values.
AND ((S.TYPE) Is Null));
Now, once I get to about 50 of these values or so, I get "query too
complex", how can I make this happen?
TIA
On Sat, 21 May 2005 23:07:59 -0700, ynott <yn***@hotmail.com> wrote:
Put the values in a table. Then write:
SELECT ... WHERE s.StreetName not in (select <FieldName> from
<NewTable>)
-Tom.
Novice here obviously. Anyway, I''m a bit confused about how to
approach this. I have a query against a table that I''m trying to
create. I''m trying to view all streets.type values that are null, but
then exclude those STREETNAME values that are null for the S.type but
that are really okay. The query goes like:
SELECT S.ID, S.ID2, S.PREFIX, S.STREETNAME, S.TYPE
FROM streets as S
WHERE
S.STREETNAME Not Like "*HWY110*" AND
S.STREETNAME Not Like "*HWY111*" AND
S.STREETNAME Not Like "*HWY112*" AND
S.STREETNAME Not Like "*HWY113*" AND
S.STREETNAME Not Like "*HWY114*" AND
S.STREETNAME Not Like "*HWY115*" AND
S.STREETNAME Not Like "*HWY116*" AND
S.STREETNAME Not Like "*HWY117*" AND
S.STREETNAME Not Like "*HWY118*" AND
S.STREETNAME Not Like "*HWY119*" AND
etc for about 250 more values.
AND ((S.TYPE) Is Null));
Now, once I get to about 50 of these values or so, I get "query too
complex", how can I make this happen?
TIA
Perfect. Thx
On Sat, 21 May 2005 23:24:29 -0700, Tom van Stiphout
<no*************@cox.net> wrote:
On Sat, 21 May 2005 23:07:59 -0700, ynott <yn***@hotmail.com> wrote:
Put the values in a table. Then write:
SELECT ... WHERE s.StreetName not in (select <FieldName> from
<NewTable>)
-Tom.Novice here obviously. Anyway, I''m a bit confused about how to
approach this. I have a query against a table that I''m trying to
create. I''m trying to view all streets.type values that are null, but
then exclude those STREETNAME values that are null for the S.type but
that are really okay. The query goes like:
SELECT S.ID, S.ID2, S.PREFIX, S.STREETNAME, S.TYPE
FROM streets as S
WHERE
S.STREETNAME Not Like "*HWY110*" AND
S.STREETNAME Not Like "*HWY111*" AND
S.STREETNAME Not Like "*HWY112*" AND
S.STREETNAME Not Like "*HWY113*" AND
S.STREETNAME Not Like "*HWY114*" AND
S.STREETNAME Not Like "*HWY115*" AND
S.STREETNAME Not Like "*HWY116*" AND
S.STREETNAME Not Like "*HWY117*" AND
S.STREETNAME Not Like "*HWY118*" AND
S.STREETNAME Not Like "*HWY119*" AND
etc for about 250 more values.
AND ((S.TYPE) Is Null));
Now, once I get to about 50 of these values or so, I get "query too
complex", how can I make this happen?
TIA
Tom
Have you tried this? Is it slow? I have found "NOT IN" to be very, very
slow. For this reason I would concur with putting the values into a
table, but I would suggest using a JOIN in the SQL and a WHERE
CriteriaTable.StreetName Is Null.
But perhaps that idea is no longer relevant and SQL and JET or whatever
will optimize the NOT IN condition so that its execution is fast?
这篇关于查询过于复杂。如何编码不正确的声明的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!