php 如何从mysql结果中选择第一个和最后一个数据行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4899042/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to select first and last data row from a mysql result?
提问by Kay
SELECT * from Userreturns 75 users. Is it possible to select 1st user, and 75th user without doing while($row = mysql_fetch_assoc($result))?? and how?
SELECT * from User返回 75 个用户。是否可以在不做的情况下选择第 1 个用户和第 75 个用户while($row = mysql_fetch_assoc($result))？如何？
UPDATE
更新
Just to be more clear: I need to have SELECT *as I need the first and 75th user before I do while mysql_fetch_assocso ASC, DESC, LIMITanswers not required.
只是为了更清楚：我需要SELECT *为我所需要的第一和第75的用户之前，我做while mysql_fetch_assoc这样ASC, DESC, LIMIT解答了不是必需的。
回答by Saul
SELECT * from User LIMIT 1
UNION
SELECT * from User LIMIT 74,1
Edit
编辑
@Kay: PHP can't change the internal order of the resultset after it's created.
@Kay：PHP 无法在创建结果集后更改其内部顺序。
If the query always returns 75 rows then the only way to access the 1st and the 75th before anything else would be to use mysql_data_seekwhich moves the internal result pointer:
如果查询总是返回 75 行，那么在其他任何事情之前访问第 1 行和第 75 行的唯一方法是使用移动内部结果指针的mysql_data_seek：
$result = mysql_query('SELECT * from User');
mysql_data_seek($result, 1);
$row1 = mysql_fetch_assoc($result);
mysql_data_seek($result, 75);
$row75 = mysql_fetch_assoc($result);
Note that if the above is followed by a while, the pointer must be reset to a suitable position.
请注意，如果上述后跟 a while，则必须将指针重置到合适的位置。
回答by alex
If you can sort it, you can.
如果你可以排序它，你可以。
Select * from User order by [something] asc limit 1
and
和
Select * from User order by [something] desc limit 1
回答by Mick Hansen
Assuming you have 'id' as a primary key and you need the last one (not the 75th one) you could try something like:
假设您将 'id' 作为主键，并且您需要最后一个（不是第 75 个），您可以尝试以下操作：
SELECT * FROM User WHERE id IN ((SELECT min(id) FROM user b), (SELECT max(id) FROM user c))
回答by Waruna Manjula
SELECT
(SELECT column FROM table WHERE [condition] ORDER BY column LIMIT 1) as 'first',
(SELECT column FROM table WHERE [condition] ORDER BY column DESC LIMIT 1) as 'last'
回答by Lieven Keersmaekers
SELECT u.*
FROM Users u
INNER JOIN (
SELECT MIN(UserID) AS UserID FROM Users
UNION ALL SELECT MAX(UserID) FROM Users
) um ON um.UserID = u.UserID
Edit
编辑
I'm not sure I completely understand what you need but following gets the first and last user followed by everyone else.
我不确定我是否完全理解您的需求，但关注第一个和最后一个用户，然后其他人关注。
SELECT um.SortOrder, u.*
FROM Users u
INNER JOIN (
SELECT 1 AS SortOrder, MIN(UserID) AS UserID FROM Users
UNION ALL SELECT 2, MAX(UserID) FROM Users
) um ON um.UserID = u.UserID
UNION ALL
SELECT 3 AS SortOrder, u.*
FROM Users u
LEFT OUTER JOIN (
SELECT MIN(UserID) AS UserID FROM Users
UNION ALL SELECT MAX(UserID) FROM Users
) um ON um.UserID = u.UserID
WHERE um.UserID IS NULL
ORDER BY
SortOrder
回答by FONGOH MARTIN
Well, the title of your question is a bit different from the explanation you gave.
嗯，你的问题的标题和你给出的解释有点不同。
I say so cos if you want to select first and last row, it might be different to select 1st and 75th row cos in case the rows increase or reduce, the last might not be the 75th row.
我这么说是因为如果你想选择第一行和最后一行，选择第一行和第 75 行可能会有所不同，以防行数增加或减少，最后可能不是第 75 行。
To answer the part of the first and last row, i think you can do this.
要回答第一行和最后一行的部分，我认为您可以这样做。
select distinct(id) from users where id in ((select max(id) from user), (select min(id) from users));
This query will work well in the hope that users are ordered by id.
此查询将运行良好，希望用户按 id 排序。
But if you are talking about the 1st and 75th row, then i'll settle with @Saul's answer .
但是，如果您谈论的是第 1 行和第 75 行，那么我将接受@Saul 的回答。
Hope this helps.
希望这可以帮助。
