当前位置：文江博客话题详情

MySQL 嵌套查询执行

发布于 2024-09-13 18:37:55 字数 84 浏览 6 评论 0原文

我正在编写一个嵌套的 MySQL 查询，其中子查询返回多于一行，因此该查询无法执行。

谁能建议我解决这个问题？

提前致谢。

原文

分享到QQ

分享到微博

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

发布评论

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

十二 2024-09-20 18:37:55

关于子查询返回多个值的错误告诉我，您正在尝试进行直接值比较，如下所示：

WHERE col = (SELECT col2 FROM TABLE_2)

解决方案取决于来自子查询的数据 - 您希望查询使用返回的所有值吗？如果是，则更改 IN 的等号：

WHERE col IN (SELECT col2 FROM TABLE_2)

否则，您需要更正子查询，使其仅返回一个值。 MAX 或 MIN 聚合函数是一种可能 - 它们将返回最高或最低值。这可能只是关联子查询的问题：

  FROM TABLE_1 t1
WHERE t1.col = (SELECT MAX(t2.col2)
                           FROM TABLE_2 t2
                         WHERE t2.fk_col = t1.id)  -- correlated example

正如 Tabhaza 指出的，子查询通常不会返回多于一列（尽管某些数据库支持元组匹配），在这种情况下，您需要定义派生表/内联视图并加入它。

如果能获得有关您遇到的问题的更多信息，那就太好了......

An error about a subquery returning more than one value says to me that you're attempting a straight value comparison, like this:

WHERE col = (SELECT col2 FROM TABLE_2)

The solution depends on the data coming from the subquery - do you want the query to use all the values being returned? If yes, then change the equals sign for an IN:

WHERE col IN (SELECT col2 FROM TABLE_2)

Otherwise, you need to correct the subquery so it only ever returns one value. The MAX or MIN aggregate functions are a possibliity - they'll return the highest or lowest value. It could just be a matter of correlating the subquery:

  FROM TABLE_1 t1
WHERE t1.col = (SELECT MAX(t2.col2)
                           FROM TABLE_2 t2
                         WHERE t2.fk_col = t1.id)  -- correlated example

As Tabhaza points out, a subquery generally doesn't return more than one column (though some databases support tuple matching), in which case you need to define a derived table/inline view and join to it.

Would've been nice to have more information on the issue you're having...

回复收藏 0 原文

メ斷腸人バ 2024-09-20 18:37:55

尝试连接到派生表而不是执行子查询；它将允许您返回多个字段：

SELECT a.Field1, a.Field2, b.Field3, b.Field4
FROM table1 a INNER JOIN 
  (SELECT c.Field3, c.Field4, c.Key FROM table2 as c) as b ON a.Key = b.Key
WHERE ...

Try joining to a derived table rather than doing a subquery; it will allow you to return multiple fields:

SELECT a.Field1, a.Field2, b.Field3, b.Field4
FROM table1 a INNER JOIN 
  (SELECT c.Field3, c.Field4, c.Key FROM table2 as c) as b ON a.Key = b.Key
WHERE ...

回复收藏 0 原文