sql查询中相交操作的问题
我编写了以下查询,我认为它是正确的,但我有一个“缺少运算符”错误。
SELECT * FROM results,Types WHERE results.a=Types.b
INTERSECT SELECT * FROM results,Types WHERE results.c=Types.b
有人可以帮我吗?
多谢。
I wrote the following query, I think it's correct but I have a "missing operator" error.
SELECT * FROM results,Types WHERE results.a=Types.b
INTERSECT SELECT * FROM results,Types WHERE results.c=Types.b
Could somebody help me please?
Thanks a lot.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(4)
你使用什么数据库?您确定支持
intersect
吗?我在 Oracle 上尝试了您的查询(将表名称更改为与我的数据库相对应的名称),并且运行正常。编辑:
既然您确认您正在使用 MS-Access,那么很明显 INTERSECT 是问题所在,因为 MS-Access 不支持它: http://www.access-programmers.co.uk/forums/archive/index.php/t-86531.html
编辑2:
这未经测试,但基本思想是您需要查找第一个查询中存在于第二个查询中的所有行。为此,您必须比较两个查询之间的每一列是否匹配,因为所有列都必须匹配才能成为“相交”行。
可能存在一些语法问题,但希望这可以帮助您入门。
What database are you using? Are you sure that
intersect
is supported? I tried your query on Oracle (changing the table names to something corresponding to my DB) and it worked ok.EDIT:
Since you confirmed you are using MS-Access, then it's clear that INTERSECT is the problem since it's not supported with MS-Access: http://www.access-programmers.co.uk/forums/archive/index.php/t-86531.html
EDIT2:
This is untested, but the basic idea is that you need to find all rows in your first query that exist in your second query. To do that, you will have to compare every column between the 2 queries for a match, as all the columns must match for it to be an "intersected" row.
There may be some syntax issues, but hopefully this gets you started.
非 ANSI 连接可能会造成混乱
It's possible the non-ANSI join is confusing matters
我建议单独测试每个 SELECT 语句。确保他们自己工作。然后进行相交。
例如,如果您使用 SQL Server,那么这不是引用两个表的正确方法。您需要添加一个
JOIN
子句(使用ON
指定要JOIN
的列)。即使不是 SQL Server,您也需要确保每个查询都能独立运行。
编辑:其他人在这里询问了如何在 MS Access 中实现 SQL INTERSECT 和 MINUS 操作。我发现存在意见分歧,因此请务必测试您的结果以确保您得到您想要的结果。
I would suggest testing each of the
SELECT
statements separately. Make sure they work by themselves. Then do theINTERSECT
.If you are working with SQL Server, for example, that is not the correct way to refer to two tables. You would need to add a
JOIN
clause (with anON
specifying the columns toJOIN
).Even if it isn't SQL Server, you need to make sure that the queries each work on their own.
Edit: Someone else has asked here about How can I implement SQL INTERSECT and MINUS operations in MS Access. I see a difference of opinion there, so be sure to test your results to make sure you're getting what you want.
仅当您想查找各个查询产生的公共线时才使用 INTERSECT 语句。
您的语句:
“ SELECT * FROM results,Types WHERE results.a=Types.b
INTERSECT SELECT * FROM results,Types WHERE results.c=Types.b "
与关节工作所需的条件不匹配。"WHERE" 子句造成了问题。
If results.a<>results.c :than你...没有什么可以相交的。
Use INTERSECT statement only if u want to find common line produce by the individual querys.
You're statement:
" SELECT * FROM results,Types WHERE results.a=Types.b
INTERSECT SELECT * FROM results,Types WHERE results.c=Types.b "
don't match the criteria needed for the joint to work."WHERE" clause is creating the problem.
If results.a<>results.c :than u...have nothing to INTERSECT.