主页 / dba / 问题 / 33905
Accepted
Phil
Asked: 2013-02-01 07:50:29 +0800 CST

如何使用从第一个查询中选择值执行两个简单查询

  • 772

我需要执行选择操作并获取 id 列的值（返回单个值）。使用这个值，我需要使用 UNION ALL 在同一个表上执行另外两个选择（为了选择 limit n id < selected.id 和 limit n id > selected.id

我目前无法从第一个查询中获取值来执行联合所有查询。我该如何解决？

SELECT * FROM table WHERE column_b = 'a value' # this query returns ID.
union all  
(select a.* from table as a where id < returned.id
order by id desc limit 5) 
union all  
(select a.* from table as a where id >= returned.id
order by id asc limit 15)
mysql union

2 个回答

  1. 如何在联合 SELECT 中使用连接：

    (SELECT a.* 
 FROM table AS a
  JOIN table AS t2 ON (a.id < t2.id)
 WHERE t2.column_b = 'a value'
 ORDER BY id DESC LIMIT 5) 
UNION ALL
(SELECT a.*
 FROM table AS a
  JOIN table AS t2 ON (a.id >= t2.id)
 WHERE t2.column_b = 'a value'
 ORDER BY id ASC LIMIT 15)

    请注意，来自 UNION ALL 部分的顺序可能不会保留在结果中。

    • 1
  2. Best Answer

    为什么不实际执行此操作

    SELECT ID INTO @ReturnedID FROM table WHERE column_b = 'a value';
select * from table as a where id = @ReturnedID
union all  
select * from table as a where id < @ReturnedID order by id desc limit 5
union all  
select * from table as a where id >= @ReturnedID order by id asc limit 15
;
    • 1

相关问题