MySQL 子查询不工作，生成错误代码 1064

发布于 2024-10-07 16:18:49 字数 578 浏览 0 评论 0原文

该查询不起作用

SELECT * FROM Catalogue WHERE Catalogue.ID IN (
    SELECT ID_catalogue FROM Categories WHERE Categories.Product_type='xxx'
)

错误代码：1064 你有一个错误你的 SQL 语法靠近 'SELECT ID_catalogue 来自类别 WHERE Categories.Product_type='xxx') ' 在第 2 行

行好的，那是因为我使用的是非常旧的 MySQL 版本。

我想要得到的是

SELECT * FROM Catalogue WHERE Product_type='xxx' OR Catalogue.ID IN (
    SELECT ID_catalogue FROM Categories WHERE Categories.Product_type='xxx'
)

有没有同等的东西？

感谢您的所有评论。

原文

This query doesn't work

SELECT * FROM Catalogue WHERE Catalogue.ID IN (
    SELECT ID_catalogue FROM Categories WHERE Categories.Product_type='xxx'
)

Error Code : 1064
You have an error in
your SQL syntax near 'SELECT
ID_catalogue FROM Categories WHERE
Categories.Product_type='xxx' ) ' at
line 2

Ok, that's because I am using a very old MySQL version.

What I am trying to get is

SELECT * FROM Catalogue WHERE Product_type='xxx' OR Catalogue.ID IN (
    SELECT ID_catalogue FROM Categories WHERE Categories.Product_type='xxx'
)

Is there any equivalent for that?

Thank you for all your comments.

分享到QQ

分享到微博

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

发布评论

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

野稚 2024-10-14 16:18:49

如果您使用的 mysql 版本 <= 4.0 - 那么这就是原因，因为在 4.1 中添加了子查询

     SELECT c.*,
            g.ID_catalogue
       FROM Catalogue c
  LEFT JOIN Categories g ON g.ID_catalogue = c.ID
                        AND g.Product_type='xxx'
     HAVING ID_catalogue IS NOT NULL
         OR Product_type = 'xxx'

您应该为表 Categories 添加复合索引 ID_catalogue + Product_type AND表 Catalogue 的索引 Product_type 可以快速执行此查询

If you're using mysql version <= 4.0 - then it is the reason, since subqueries were added in 4.1

     SELECT c.*,
            g.ID_catalogue
       FROM Catalogue c
  LEFT JOIN Categories g ON g.ID_catalogue = c.ID
                        AND g.Product_type='xxx'
     HAVING ID_catalogue IS NOT NULL
         OR Product_type = 'xxx'

You should add composite index ID_catalogue + Product_type for table Categories AND index Product_type for table Catalogue to have this query performed fast

回复收藏 0 原文