MySQL：围绕 id 列进行间隔，并从具有多列的子查询中返回另一个间隔

发布于 2024-12-12 14:20:41 字数 589 浏览 0 评论 0原文

我想从内容如下的表中运行查询：

id | col1 | col2 | col3
-----------------------
1  | i_11 | i_12 | i_13
2  | i_21 | i_22 | i_23
3  | i_31 | i_32 | i_33
.. | ...  | ...  | ...

SELECT col1 FROM table WHERE id IN
(SELECT id-1, id+1 FROM table WHERE col1='xxx' AND col2='yyy' AND col3='zzz')

目的是根据 id 获取间隔 [id-1, id+1] > 列，返回 col1 中存储的 id-1 和 id+1 内容。子查询有效，但我想查询本身有问题，因为我遇到错误“操作数应仅包含一列”。我明白这一点，但我没有看到任何其他方法可以在一个查询中做到这一点？

我很确定有一个非常简单的解决方案，但我暂时无法弄清楚，即使在仔细阅读了有关多列子查询的其他帖子之后...

谢谢您的帮助:-)

原文

I would like to run a query from a table where the content is like that :

id | col1 | col2 | col3
-----------------------
1  | i_11 | i_12 | i_13
2  | i_21 | i_22 | i_23
3  | i_31 | i_32 | i_33
.. | ...  | ...  | ...

SELECT col1 FROM table WHERE id IN
(SELECT id-1, id+1 FROM table WHERE col1='xxx' AND col2='yyy' AND col3='zzz')

The aim is to get an interval [id-1, id+1] based on the id column which returns the content stored in col1 for id-1 and id+1. The subquery works but I guess I have a problem with the query itself, since I'm having an error "Operand should contain only one column". I understand it, but I don't see any other way to do it in one query ?

I'm quite sure there's a pretty easy solution but I can't figure it out for the moment, even after having carefully read other posts about multiples columns' subqueries...

Thank you for any help :-)

分享到QQ

分享到微博

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

发布评论

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

瞎闹 2024-12-19 14:20:42

您的问题是您正在检索两个值 - 但作为列表而不是集合。当 1,3 出现在一行中时，SQL 优化器无法将它们视为两个项目的集合。可能还需要演员阵容。

这应该有效。

SELECT col1 FROM table WHERE id in 
(
    select cast(id as int) -1 from table where col1='i_21'
    union
    select cast(id as int) +1 from table where col1='i_21'
)

Your problem is that you are retrieving two values - but as a list rather than a set. The SQL optimizer can't see 1,3 as a set of two items when they are presented in a single row. There may also be a cast needed.

This should work.

SELECT col1 FROM table WHERE id in 
(
    select cast(id as int) -1 from table where col1='i_21'
    union
    select cast(id as int) +1 from table where col1='i_21'
)

回复收藏 0 原文

燃情 2024-12-19 14:20:41

我现在能想到的唯一方法是这样的：

SELECT col1 
FROM table T
WHERE id BETWEEN (SELECT id FROM table WHERE col1='xxx' AND col2='yyy' AND col3='zzz') -1 
    and (SELECT id FROM table WHERE col1='xxx' AND col2='yyy' AND col3='zzz') +1

The only way I can think to do it right now is like this:

SELECT col1 
FROM table T
WHERE id BETWEEN (SELECT id FROM table WHERE col1='xxx' AND col2='yyy' AND col3='zzz') -1 
    and (SELECT id FROM table WHERE col1='xxx' AND col2='yyy' AND col3='zzz') +1

回复收藏 0 原文

~没有更多了~