mysql - 选择第一个和最后两个元素

Question

你好，我有一张包含这些元素的表格

id name
1 luke
2 johnny
3 perry
4 jenny
5 mark

我必须做一个获取第一个元素和最后一个元素的查询 2我这个例子

1 luke
4 jenny
5 mark

我该怎么办？

谢谢

Answer 1

我不认为你可以用一个查询来做到这一点:我会说你必须使用两个查询:

一、获取第一个结果:

select *
from your_table
order by id asc
limit 1

另一个获得最后两个结果——以相反的方向排序并获得前两个结果就可以了:

select *
from your_table
order by id desc
limit 2

之后，您可以只发送一个查询，而不是从您的编程语言向 SQL 服务器发出两个请求，该查询将使用 UNION 来获取两个结果:

(select * from your_table order by id asc limit 1)
UNION
(select * from your_table order by id desc limit 2)

但是，考虑一下......不确定这是否真的可行，在每个子查询中有一个带顺序和限制的 UNION......

---

编辑:我做了测试，似乎是可行的:

这是独立执行的两个查询:

mysql> select id, title from post order by id asc limit 1;
+----+--------------+
| id | title        |
+----+--------------+
|  1 | Premier post |
+----+--------------+
1 row in set (0,00 sec)

mysql> select id, title from post order by id desc limit 2;
+----+-------------------------+
| id | title                   |
+----+-------------------------+
|  7 | Septième post          |
|  6 | Sixième post (draft=7) |
+----+-------------------------+
2 rows in set (0,00 sec)

下面是 UNION 的样子:

mysql> (select id, title from post order by id asc limit 1) UNION (select id, title from post order by id desc limit 2);                                                                                                                                                       
+----+-------------------------+
| id | title                   |
+----+-------------------------+
|  1 | Premier post            |
|  7 | Septième post          |
|  6 | Sixième post (draft=7) |
+----+-------------------------+
3 rows in set (0,03 sec)

但是请注意，3 个结果行的顺序没有很好地定义...

并且，引用了 MySQL 5.1 的以下手册页:12.2.8.3. UNION Syntax :

To apply ORDER BY or LIMIT to an individual SELECT, place the clause inside the parentheses that enclose the SELECT:

(SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
UNION
(SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);

However, use of ORDER BY for individual SELECT statements implies nothing about the order in which the rows appear in the final result because UNION by default produces an unordered set of rows.