是否可以合并到 SQL 中的 SELECT 中？

发布于 2024-12-19 12:37:12 字数 262 浏览 0 评论 0原文

假设我想从名为 messages 的表中检索 100 条记录，并且希望通过以下方式获取它们：

1st message
100th message
2nd message
99th message
3rd message
98th message
(...)

有什么方法可以高效地执行此操作吗？合适的查询是什么？或者我应该进行一个查询来选择前 50 个，一个查询来选择最后 50 个，然后合并结果？

原文

Let's say I want to retrieve 100 records from a table called messages, and I want to obtain them the following way:

1st message
100th message
2nd message
99th message
3rd message
98th message
(...)

Is there any way to do this efficiently? What would be the appropriate query?
Or should I make a query to select the first 50, a query to select the last 50 and then merge the results?

分享到QQ

分享到微博

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

发布评论

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

沒落の蓅哖 2024-12-26 12:37:12

尝试一下您的 ID 是否是数字序列：

首先

SET @half = (SELECT MAX(id) FROM messages)/2;

然后

SELECT * FROM `messages` ORDER BY (IF(id<@half,@half*2-id,id-1)) DESC,id ASC;

Try if your ID is a sequence of numbers:

First

SET @half = (SELECT MAX(id) FROM messages)/2;

Then

SELECT * FROM `messages` ORDER BY (IF(id<@half,@half*2-id,id-1)) DESC,id ASC;

回复收藏 0 原文

动次打次papapa 2024-12-26 12:37:12

重点是创建两个虚拟列“serie_order”（变体）和“serie”（常量），您将在数据的两个部分上使用（您必须将数据分成两部分）。

SELECT * FROM (
  SELECT 1 as serie, message_id AS serie_order , *  FROM
   (SELECT message_id FROM messages ) as part_up
UNION 
  SELECT 2 as serie, 101-message_id  as serie_order, * FROM
   (SELECT message_id FROM messages) as part_down
) AS world

ORDER BY serie_order ASC, serie ASC
LIMIT 100

The point is to create two virtual columns "serie_order" (variant) and "serie" (constant) you'll use on both parts of your data (you'll have to split your data in two).

SELECT * FROM (
  SELECT 1 as serie, message_id AS serie_order , *  FROM
   (SELECT message_id FROM messages ) as part_up
UNION 
  SELECT 2 as serie, 101-message_id  as serie_order, * FROM
   (SELECT message_id FROM messages) as part_down
) AS world

ORDER BY serie_order ASC, serie ASC
LIMIT 100

回复收藏 0 原文

澉约 2024-12-26 12:37:12

set @rank:=0; 

select id from 
(select id, @rank:=(coalesce(@rank, 0)+1) as new_order 
 from a_table 
 order by some_column limit 100) as ordering
order by if (new_order<=50, new_order-1, abs(100-new_order)) asc;

set @rank:=0; 

select id from 
(select id, @rank:=(coalesce(@rank, 0)+1) as new_order 
 from a_table 
 order by some_column limit 100) as ordering
order by if (new_order<=50, new_order-1, abs(100-new_order)) asc;

回复收藏 0 原文

~没有更多了~