MySQL 不存在的地方

发布于 2024-11-06 09:41:32 字数 261 浏览 0 评论 0原文

这段代码有什么问题？我不断收到错误 1064 (42000)：您的 SQL 语法中有错误

SELECT clientReport.id
  WHERE clientReport.rowNumber NOT EXISTS (
    SELECT clientReport.rowNumber FROM report02, clientReport
      WHERE report02.id=clientReport.id);

原文

What's wrong with this code? I keep getting ERROR 1064 (42000): You have an error in your SQL syntax

SELECT clientReport.id
  WHERE clientReport.rowNumber NOT EXISTS (
    SELECT clientReport.rowNumber FROM report02, clientReport
      WHERE report02.id=clientReport.id);

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

已下线请稍等 2024-11-13 09:41:32

我假设您想要执行类似的操作：

SELECT clientReport.id 
FROM clientReport 
LEFT JOIN report02 ON(report02.id = clientReport.id)
WHERE report02.id is null;

这将从 clientReport 返回所有在 report02 中没有相应条目的 ID。

另一种选择可能是：

SELECT clientReport.id FROM clientReport
WHERE clientReport.rowNumber NOT IN (
  SELECT clientReport.rowNumber 
  FROM report02, clientReport
  WHERE report02.id=clientReport.id);

I assume you want to do something like:

SELECT clientReport.id 
FROM clientReport 
LEFT JOIN report02 ON(report02.id = clientReport.id)
WHERE report02.id is null;

This will return all IDs from clientReport which have no corresponding entry in report02.

An alternative might be:

SELECT clientReport.id FROM clientReport
WHERE clientReport.rowNumber NOT IN (
  SELECT clientReport.rowNumber 
  FROM report02, clientReport
  WHERE report02.id=clientReport.id);

回复收藏 0 原文

聚集的泪 2024-11-13 09:41:32

您的第一个 select 语句中缺少 FROM ：

SELECT clientReport.id
  FROM clientReport '<--- need this
  WHERE clientReport.rowNumber NOT EXISTS (
    SELECT clientReport.rowNumber FROM report02, clientReport
      WHERE report02.id=clientReport.id);

You're missing a FROM on your first select statement:

SELECT clientReport.id
  FROM clientReport '<--- need this
  WHERE clientReport.rowNumber NOT EXISTS (
    SELECT clientReport.rowNumber FROM report02, clientReport
      WHERE report02.id=clientReport.id);

回复收藏 0 原文

雨轻弹 2024-11-13 09:41:32

您可能需要 NOT IN 而不是 NOT EXISTS

回复收藏 0 原文

青衫儰鉨ミ守葔 2024-11-13 09:41:32

您忘记在主查询中添加 from 子句。

SELECT clientReport.id from clientReport
  WHERE clientReport.rowNumber NOT IN (
    SELECT clientReport.rowNumber FROM report02, clientReport
      WHERE report02.id=clientReport.id);

You forgot to add from clause in main query.

SELECT clientReport.id from clientReport
  WHERE clientReport.rowNumber NOT IN (
    SELECT clientReport.rowNumber FROM report02, clientReport
      WHERE report02.id=clientReport.id);

回复收藏 0 原文

一身软味 2024-11-13 09:41:32

MySQL 服务器返回的完整错误消息是什么？您应该收到如下所示的错误消息：

You have an error in your SQL syntax near `NOT EXISTS`

您还应该考虑使用 RIGHT JOIN 而不是子查询选择，因为在本例中 RIGHT JOIN 似乎正是您想要的。

编辑：此外，由于运行子查询时观察到的性能影响，建议使用非常有选择性的 JOIN，也就是说，当 MySQL GA 实现使用时，在正常查询中再次使用子查询是可以的子查询中的 LIMIT。这将大大减少性能损失。

What is the full error message MySQL server is returning? You should get an error message like the below:

You have an error in your SQL syntax near `NOT EXISTS`

You should also consider using a RIGHT JOIN instead of a sub-query select, as a RIGHT JOIN seems to be what you want in this case.

EDIT: Additionally, because of the performance hit observed when sub-queries are run, it's advisabe to use very selective JOINs instead, that being said, it will be okay to use sub-queries again in normal queries when MySQL GA implements the use of LIMIT in sub-queries. This will reduce the performance hit greatly.

回复收藏 0 原文

~没有更多了~