MySQL select语句以循环方式返回结果 [英] MySQL select statement returning results in circle mode

问题描述

我正在使用一个应用程序，该应用程序通过滑动可以一个接一个地看到卡片.允许左右滑动.如果用户向右滑动，他会看到下一张可用的卡.如果他向左滑动，将会看到前一张卡片.

i am working with an app that via swipes you can see cards one after the other. Both left and right swipes are permitted. If the user swipes right he will see the next available card. If he swipes at the left he will see the previous card.

卡的详细信息存储在MySQL表卡"中，并具有一个唯一的ID，例如从"1.000.000"开始的"CardID"，依此类推.假设我当前在卡数"表中添加了500张卡，因此最后存储的卡ID为1.000.500.我想创建一个程序，一次根据用户不断刷卡的方向向用户发送20张卡.如果向左滑动，程序将发送前20条记录，依此类推.

The cards details are stored to a MySQL table 'Cards' and have a unique id let's say 'CardID' starting from number 1.000.000 and so on. Let's say that i currently have added 500 cards to the 'Cards' table, so the last card ID stored is 1.000.500. I want to create a procedure that will send back to the user 20 cards at a time depending on the direction he keeps swipping. If the swipes are done to the left the procedure will send the previous 20 records and so on.

现在认为用户一直向左滑动并从号码1.000.009及以下请求以前的记录.我想编写一条select语句，该语句将把剩下的9行和表底部的11行一起获取并提供给用户.那将是CardID为1.000.489到1.000.500的记录，因此当用户滑动时，当他不滑动1.000.000(第一个)时，他将绕行查看1.000.500，反之亦然

Now think that the user keeps swipping left and requests the previous records from number 1.000.009 and below. I want to write a select statement that will fetch and serve to the user those 9 rows left and the 11 rows from the bottom of the table alltogether. That would be records with CardID 1.000.489 to 1.000.500, so as the user swipes, when he will swipe no 1.000.000 (the first) he will go by circle to see 1.000.500 and vice versa

提前谢谢

推荐答案

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table (id SERIAL PRIMARY KEY);

INSERT INTO my_table VALUES 
(1),
(2),
(3),
(4),
(5),
(6),
(7),
(8),
(9);

SELECT * FROM my_table ORDER BY id > 5 DESC, id;
+----+
| id |
+----+
|  6 |
|  7 |
|  8 |
|  9 |
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
+----+

这篇关于MySQL select语句以循环方式返回结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！